You need a field which uniquely defines the ordering of the run, like a
date_time field, or something like that.
-------------------------------------
SELECT a.customer,
a.dateTimeField,
LAST(a.amount),
SUM(b.amount)
FROM tableName AS INNER JOIN tableName AS b
ON a.customer = b.customer
AND a.dateTimeField >= b.dateTimeField
GROUP BY a.customer, a.dateTimeField
----------------------------------
'a' and 'b' are aliases to your single table, to be compared to two fingers
running on a single list, but while one finger,'a', is stopped on a
{customer, dateTimeField}, the second finger, 'b', can only stops ON rows
where
a.customer = b.customer AND a.dateTimeField >= b.dateTimeField
in order to contribute to
SUM(b.amount)
You can select other fields from each group with the LAST aggregate:
SELECT a.customer,
a.dateTimeField,
LAST(a.amount),
LAST(a.somethingElse),
SUM(b.amount)
...
or by defining a new group:
SELECT a.customer,
a.accountType, ' <----
a.dateTimeField,
LAST(a.amount),
SUM(b.amount)
FROM tableName AS INNER JOIN tableName AS b
ON a.customer = b.customer
AND a.accountType=b.accountType ' <----
AND a.dateTimeField >= b.dateTimeField
GROUP BY a.customer,
a.accountType, ' <----
a.dateTimeField
Hoping it may help,
Vanderghast, Access MVP
