Re: DateDiff

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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




"MJE" <MJE@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3D41DE7F-DA0A-4E23-96AE-9E258765083E@xxxxxxxxxxxxxxxx
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!


.

Relevant Pages

  • Create customizable ranges?
    ... I have a query that calculates the difference ... between a clinic visit date to a sample collection date (Diff). ... simple table to house the range information and somehow link this ...
    (microsoft.public.access.gettingstarted)
  • diff output
    ... i have two large text-files (output of a database ... query) and i have to do a diff on them. ... updates if there are new lines or deleted lines within a certain range. ... The output of the diff is much bigger then but this does not ...
    (comp.unix.shell)
  • Re: How do Diff?
    ... I was looking to diff at a folder level or the entire project tree for 2 ... I was able to query the differences based on the label and then generate a ...
    (microsoft.public.vstudio.sourcesafe)
  • Re: how do i write if and else statement in access query
    ... > expression in access query with if and else statement to count the number of ... > day diff between date 1 and date 2. ... > display it as ‘0” value. ... > Do you have an idea how to write in the query? ...
    (microsoft.public.access.queries)