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
animalloverAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.