• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Days between transactions when the date is stored in the same column

I have a table (tblAnimalServices) with several columns, but the 3 I am interested in are AnimalID, ServiceDate, ServiceType.  I need to be able to find out how many days are between two different ServiceTypes for one AnimalID.  So for example the table might look like this

Animal ID         ServiceType                        ServiceDate
123456             Intake-Owner Release         01/01/12
123456             Adopted                               01/05/12
123456             Intake-Adoption Return      01/08/12

I need to find out how many days are between the Adopted and Intake-Adoption Return ServiceType for AnimalID 123456
0
animallover
Asked:
animallover
  • 2
1 Solution
 
GRayLCommented:
You don't care about days between Intake-Owner Release and Adopted?
0
 
peter57rCommented:
Use a function as to calculate the differences.

You need to check all field names and table names...

Function NextDateDiff(Id As Long, SDate As Date) As Integer
Dim db As Database
Dim rs As DAO.Recordset
Dim strsql
'NextDateDiff = 0
Set db = CurrentDb
strsql = "Select top 1 ServiceDate from tblAnimalServices where Animalid=" & Id & " and ServiceDate >#" & Format(SDate, "yyyy-mm-dd") & "# order by Servicedate Asc"
Set rs = db.OpenRecordset(strsql)
If rs.RecordCount = 0 Then
NextDateDiff = 0
Else
NextDateDiff = rs!Servicedate - SDate
End If
rs.Close
Set rs = Nothing
Set db = Nothing

End Function

In a query based on the table add an extra column with field set to :
Interval:NextDateDiff([AnimalID], [ServiceDate])
0
 
GRayLCommented:
I have removed the space from AnimalID to make is consistent with your naming and am assuming AnimalID is numeric.  This will provide the date difference for the animal id requested.  If you want to see the date difference for all the AnimalID's , simply remove 'and AnimalID = 123456' from the last line.

SELECT a.AnimalID, a. ServiceType, a.ServiceDate
(SELECT b.ServiceDate FROM myTable b WHERE b.ServiceType = "Intake-Adoption Return"
AND b.AnimalID = a.AnimalID) AS IARServiceDate
DateDiff("d",a.ServiceDate, IARServiceDate) as ReqdDateDiff
FROM myTable a WHERE a.ServiceType = "Adopted" and AnimalID = 123456;

If AnimalID is String, wrap it in quotes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now