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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 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