Hi,
Assuming you have a date_time field, dt, then
SELECT a.dt, LAST(b.sales)-LAST(a.sales)
FROM (myTable As a INNER JOIN myTable As b
ON a.dt < b.dt)
INNER JOIN myTable As c
ON a.dt < c.dt
GROUP BY a.dt, b.dt
HAVING MIN(c.dt)= b.dt
Basically, we got three references to the original data. we group on each
date_time of a, each date_time of b that are occurring after a, and let c.dt
floating. When the groups are made, (with all possible combinations of a.dt
and b.dt), we keep only those where b.dt = MIN(c.dt), ie, only those of b.dt
immediately following a.dt.
There is a rule telling that we must either include the field in the group
by, either aggregate it, so here, we use LAST over the sales fields, to get
the single value associated to a given reference a, or b.
Hoping it may help,
Vanderghast, Access MVP
