DateDiff

M

MJE

I have a table that looks like this:

Aircraft Event Date
1 1/1/06
1 2/1/06
2 1/1/06
1 3/1/06
2 2/1/06

I need a query that provides the difference between each pair of dates
(there may be up to 20 pairs) similar to the following:

Aircraft Datedif1 Datedif2 .... Datedif(n)

I've tried using crosstab queries, but am having no luck. Any suggestions
would be appreciated!
 
M

Michel Walsh

Hi,




SELECT a.aircraft, a.eventDate, b.eventDate, a.eventDate-b.eventDate As diff

FROM ( myTable As a INNER JOIN myTable As b
ON a.aircraft = b.aircraft AND a.eventDate < b.eventDate)
INNER JOIN myTable As c
ON a.aircraft = c.aircraft AND a.eventDate < c.eventDate

GROUP BY a.aircraft, a.eventDate, b.eventDate

HAVING b.eventDate = MIN(c.eventDate)




You can then use a crosstab on the result of that query if you want an
horizontal display, with all possible diff (as columns), aircraft as group
(lines) and COUNT (as cell).


Hoping it may help,
Vanderghast, Access MVP
 
Top