Date Difference-Same Field-Same Table

[sgladie@bgsu.edu]

Hello, I am using Access 2013.
I am trying to create a query that will count the days difference between two dates. The dates are in the same field. I want to group by Region.
So:
tblRegion = RegionID
tblStatus = StatusDate

I know how to use the DateDiff when it is two different fields, but I can't figure out how to do it from the same field.

I appreciate the help!

 

[plog]

Can you demonstrate your issue with sample data? Provide 2 sets:

A. Starting sample data from your table. Include table and field names and enough sample data to cover all cases.

B. Using A, show what you expect as results.

 

[pbaldy]

One way would be a method like this to get the 2 dates in one record:

http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[sgladie@bgsu.edu]

I have multiple tables:
tblRegion - RegionID, Region
tblProperty - PropertyID, Property, RegID(foreignkey)
tblAddress - AddID, Address, PropID(FK)
tblUnits - UnitID, Unit, ADDID(FK)
tblStatus - StatusID, Status, StatusDate, UnitID(FK)

I need to group and find date difference for each. So my first query I have:

UnitID, StatusDate

So let's say UnitID: "1", has two statusdates. 1/9/2015 and 3/1/2015. The difference is 51 days. However, I cannot come up with the query to take the last entry (3/1/2015), and find previous date (1/9/2015).

Desired Outcome:
Unit ID, StatusDate, PreviousDate, DateDifference

Grouped by UnitID

I really appreciate any help!!

 

[plog]

Just data, no explanation. It seems like we are working with just tblStatus, so give me data from tblStatus. Then provide me with what you expect when your query runs. Also, provide enough sample data so that you can cover all cases (e.g. 5 UnitID records in tblStatus, 1 UnitID record in tblStatus, 2 UnitID records in tblStatus with same StatusDate, etc).

2 sets of data--starting and expected results.

 

[sgladie@bgsu.edu]

Here is an example. Black Fields are within the tables given.
Red Fields are what I am trying to accomplish. I hope this helps - thank you!

So basically - I need to know For each unit (group) how many days it was that in that particular status, and how many days since last status change.

Please let me know if you have further questions.

Thanks!

 

[plog]

[PreviousDate] is a misnomer. I mean, every [PreviousDate] value you posted occurs after the [StatusDate] in your example. 'NextDate' would be a better name. Additionally, to get the [DaysStatusChange] you do need the actual previous date as well.

I think the below query will give you a good start on building your final query. Copy this SQL into a new query and run it:

SELECT tblStatus.UnitID, tblStatus.StatusDate, IIf(IsNull(DMax("[StatusDate]","tblStatus","[UnitID]=" & [UnitID] & " AND [StatusDate]<#" & [StatusDate] & "#")),[StatusDate],DMax("[StatusDate]","tblStatus","[UnitID]=" & [UnitID] & " AND [StatusDate]<#" & [StatusDate] & "#")) AS PriorDate, IIf(IsNull(DMin("[StatusDate]","tblStatus","[UnitID]=" & [UnitID] & " AND [StatusDate]>#" & [StatusDate] & "#")),Date(),DMin("[StatusDate]","tblStatus","[UnitID]=" & [UnitID] & " AND [StatusDate]>#" & [StatusDate] & "#")) AS NextDate
FROM tblStatus;

It gives you both the next and prior status date for every record. With that you can build another query, bring in any other tables you need data from and do DateDiff calculations to get [DaysinStatus] and [DaysStatusChange] values. Let me know if you have any questions.

 

[sgladie@bgsu.edu]

PLOG - thank you so much! You are the best - it works like a charm!! Have a great day!