Complex Query?

[georgesOne]

Dear Experts,

I have a table with following structure

Incident TrackStart Date
A 1/15/07 1/15/07
B 1/15/07 1/18/07
C 1/15/07 1/22/07

Now I want to create a query, which tells me how many days passed since the last incident:

Incident TrackStart Date DaysSinceLastIncident
A 1/15/07 1/15/07 0
B 1/15/07 1/18/07 3
C 1/15/07 1/22/07 4

Thanks for any suggestion.
Georges

 

[georgesOne]

The above question more general:
How do I refer to the next smaller date in the table?

Thanks, Georges

 

[JerryKlmns]

SELECT Incident, TrackStart, [Date], DateDiff ("d", [Date], Date()) As DaysSinceLastIncident
FROM yourTableNameHere
ORDER BY 4;


I would strongly suggest that you rename your "Date" field to a non-reserved word one like IncidentDate

 

[georgesOne]

Thanks for taking care of my issue, Jerry.
I guess I have described the problem insufficiently.
Actually my table monitors the downtime of various machines (approx 12) for various error types (approx 15) and for various intervals (months, quarters, years).
What I did (and I came to a result which looks reasonable) was to group machines+errors+intervals, order them by the error start time and give them a ranking (if it is the first incident, of course the track date is used as first incident). Then I can refer to the rank-1 dataset to get the last/previous date of the same error, so that I can determine the time between same errors (which is used to make further calculations).
I do not think it is very elegant, but it seems to work. Unfortunately, the table design is pretty complex and it would be very hard to understand the details, if I post them.
Sorry, I thought the description of the tables as given above would be sufficient.
Your suggestion works well for the problem as described and I learned that you can order by column number.
Regards, Georges