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

Posted on 2012-03-20
Last Modified: 2012-06-11
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
Question by:animallover
  • 2
LVL 44

Expert Comment

ID: 37745431
You don't care about days between Intake-Owner Release and Adopted?
LVL 77

Expert Comment

ID: 37746494
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
NextDateDiff = rs!Servicedate - SDate
End If
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])
LVL 44

Accepted Solution

GRayL earned 500 total points
ID: 37749266
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.

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question