Maximum Number of Days Between Dates

[SmokyMtnzz]

Hello,
I have a table (tblAccidents) with a field name of Accident_Date
populated with dates. I am looking for a query that will go through
all the dates and give me a number showing the maximum number of days
between dates. In other words, the longest number of days between
accidents. (Maximum number of accident free days.)

Thanks for any help you can provide.

Bob
(SmokyMtnz)

 

[Jeff Boyce]

Bob

I'm not clear on whether your Accident_Date field has many dates in a single
row's field, or if you are saying that you have one date in EACH row's field
(the latter is better design).

If you are looking for the greatest difference between the Accident_Dates,
take a look at the Totals queries. I'm a little concerned, though ... if
ALL of your accident dates are in this table and you want to see the biggest
difference, wouldn't that be the difference between the minimum date and the
maximum date? How will Access know which "pairs" to compare?

And more concern ... are you keeping separate tables, all identically
structured, to record accident dates for "other" things (you didn't mention
what this db is about, did you?)? If so, you'll want to consider further
normalization to get the most out the features Access offers.

 

[John Spencer (MVP)]

Given dates like the following in a series of records.
1/1/04
1/5/04
1/25/04
2/3/04

The user would like to calculate the number of days between each consecutive
pair of dates and then choose the range which has the greatest gap. In the
example that pair would be 1/5/04 to 1/25/04. Of course, there could be
multiple sets of dates that would have the same range.

SELECT TOP 1
A.AccidentDate,
A.AccidentDate - DMax("AccidentDate","TblAccidents","AccidentDate<#" &
A.AccidentDate & "#")
From tblAccidents as A
ORDER BY A.AccidentDate - DMax("AccidentDate","TblAccidents","AccidentDate<#" &
A.AccidentDate & "#") DESC

That is not very efficient with a large data set, but with a small data set it
may be a viable solution.

Otherwise, the following UNTESTED SQL might work

SELECT Top 1 A.AccidentDate, B.AccidentDate
FROM tblAccidents as A INNER JOIN tblAccidents as B
On A.AccidentDate > B.AccidentDate
GROUP BY A.AccedentDate, B.AccidentDate
ORDER BY A.AccidentDate - Max(B.AccidentDate) Desc