Comparing dates from different records

[Guest]

I have a date ordered field and have been asked to identify the number of
days since the last 24 hour or more period with no orders. So if I have
orders on 5/1/07, 5/2/07, none on 5/3/07 and then an order on 5/4/07 have to
create the value 5/3/07 using the dates where I do have orders, and compare
it to the current date (which I think would just be a straight subtraction).
I can't figure out how to idenitfy the gaps between orders since each record
has only a singel date field. Thanks in advance for any suggestions (PS I'm
not much of a VB coder but can copy and paste

 

[Marshall Barton]

I have a date ordered field and have been asked to identify the number of
days since the last 24 hour or more period with no orders. So if I have
orders on 5/1/07, 5/2/07, none on 5/3/07 and then an order on 5/4/07 have to
create the value 5/3/07 using the dates where I do have orders, and compare
it to the current date (which I think would just be a straight subtraction).
I can't figure out how to idenitfy the gaps between orders since each record
has only a singel date field.

You can use a subquery to determine the latest date before
each record's date.

SELECT [date ordered],
(SELECT Max(X.[date ordered]
FROM yourtable As X
WHERE X.[date ordered] < yourtable.[date ordered]
) As PreviousDate
FROM yourtable