Calculate difference in dates in the same field

[GS500]

I’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc.

I currently have the following tables set up:

tblClaim
ClaimID
ClaimNumber

tblEvents
EventID
EventName

tblClaimEvents
ClaimEventID
fkClaimID
fkEventID
EventDate

One of the main goals of this database will be to calculate the length of time between dates, i.e. Report Date to Payment Date = 15 days. How would I write a query to calculate that with the dates all being in the same EventDate field?

I will have to do the same calculation in a form also, for example if Event A occurs before Event B, then calculate time from Event A to B.

Thanks

 

[John Big Booty]

Check out the DateDiff() function.

 

[GS500]

I have used DateDiff before to get the difference in two differentdate fields, but in this case all the dates are in the same field. That's where I'm stuck, in how to refer to both dates in the same field/Column.

 

[jdraw]

see Get the value in another record
at http://allenbrowne.com/subquery-01.html

 

[plog]

How would I write a query to calculate that with the dates all being in the same EventDate field?

Most likely you would first need to create sub-queries to identify the different types of dates from your tblClaimEvents. Something like:

SELECT fkClaimID, EventDate AS ReportDate 
FROM tblClaimEvents INNER JOIN tblEvents On tblClaimEvents.fkEventID = tblEvents.EventID
WHERE EventName="Report Date";

You also build similar queries for the other EventName values you want to use in calculations. Then you link those sub-queries together via the fkClaimID fields and you will be able to use the DateDiff() function John mentioned.

Be careful though--if you have multiple EventName values for the same fkClaimID, you will get multiple results in that final query because of them. I'm sure you have assumptions about tblClaimEvents, like a fkClaimID can only have one Report Date, but I'd first verify those assumptions are correct before relying on them to just be true.

 

[GS500]

OK, I think I got it. I created the queries like in your example, plog, and saved them, then used them as a source for a query to tie them together.

SELECT tblClaim.ClaimNum, sqryPaymentDate.fkClaimID,
 sqryReportDate.ReportDate, sqryPaymentDate.PaymentDate, 
DateDiff("d",[ReportDate],[PaymentDate]) AS ReporttoPayment
FROM tblClaim INNER JOIN (sqryPaymentDate INNER JOIN sqryReportDate ON
 sqryPaymentDate.fkClaimID = sqryReportDate.fkClaimID) ON
 tblClaim.ClaimID = sqryReportDate.fkClaimID;

Something I just thought of, couldn't I use a crosstab query with the EventName as Column Heading, ClaimID as Row Heading and EventDate as value, something like this:

TRANSFORM First(tblClaimEventsDates.EventDate) AS FirstOfEventDate
SELECT tblClaimEventsDates.fkClaimID
FROM tblEvents INNER JOIN tblClaimEventsDates ON tblEvents.EventID = tblClaimEventsDates.fkEventID
GROUP BY tblClaimEventsDates.fkClaimID
PIVOT tblEvents.EventName;

To get the EventName's into their own columns? That seems to work as well.
Also, thanks for the warning plog, I did have those assumptions, how'd you know? I’ll double-check that we’re only allowing one of each event per claim.

 

[plog]

Four hundred times bitten, twice shy. I've always found cross-tabs finicky and slow. This may not be the case in your instance though, technically it should work, run it and see if it delivers what you need efficiently.