[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2012-03-20
3
Medium Priority
?
223 Views
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
0
Comment
Question by:animallover
  • 2
3 Comments
 
LVL 44

Expert Comment

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

Expert Comment

by:peter57r
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
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
 
LVL 44

Accepted Solution

by:
GRayL earned 2000 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.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

591 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