There are many possible ways to do it, and all of them imply referencing the
table at least twice. Itis probably easier to understand with subquery, but
here a way to do it with joins:
SELECT a.asDateOf,
LAST(a.balance) As current,
LAST(b.balance) AS previous,
(current-previous)/current AS pcChange
FROM (tableNameHere AS a
LEFT JOIN tableNameHere AS b
ON a.asOfDate > b.asOfDate)
LEFT JOIN tableNameHere AS c
ON a.asOfDate > c.asOfDate
GROUP BY a.asOfDate, b.asOfDate
HAVING b.asOfDate = MAX(c.asOfDate)
Note: that assumes balance is never equal to zero, else, a division by zero
error will occur.
While the GROUPs are only relevant about the 'asOfDate', where reference b
implies all date prior the one hold under column a.asOfDate, the third
reference, c, is similar to the reference b, as it spans only dates prior
a.asOfDate (see the join ON clauses), but c is not part of any group, so its
MAX necessary returns the latest date occuring before a.asOfDate. That
latest date is the only one that will be kept, for b, and so, s.balance
become the balance value for the date occuring immediatly before a.asOfDate,
which is what we describe, in common every day speech, "the previous day
balance".
Using LAST on balance (for references a and b) is required by the SQL
syntax. Indeed, assuming there is just ONE record for ONE given asOfDate, we
could have used, as well, MIN, MAX, or even AVG, in theory,but with Jet,
LAST is the most sensible aggregate to use, not because it refers to the
latest value, but because it suggests we are interested in any one record
for the group (and either there is only one record by group, either the 'n'
records of a given group all have the same value).
Vanderghast, Access MVP
