Access 97 Queries

  • Thread starter Thread starter Bob Stevens
  • Start date Start date
B

Bob Stevens

Is there a way to create a running total for a table that
has dollars in decending order. I would like to accumulate
the total of each record?

Example:

Description Cost Accum Value
red truck $8.00 $ 8.00
Blue Truck $7.00 $15.00
Green Truck $6.00 $21.00

Bob Stevens
 
Hi Bob, you can't do this in a query. You should investigate doing it in a
report (though I suspect you will need to use VBA).
 
SELECT Description, Cost,
DSUM("Cost","SomeTable","Cost>=" & [cost]) as AccumValue
FROM SomeTable
ORDER BY Cost Desc;

This will give you duplicate totals when the costs are duplicated.
 
You need a query, qryRunningSum_Simple, like this :

SELECT T1.Description, T1.Cost,
(SELECT SUM(Cost) FROM tblMyDollarTable AS T2 WHERE
T1.Cost<=T2.Cost) AS [Acumul Value]
FROM tblMyDollarTable AS T1;

Mind you, you'll have to type this in the SQL window.

And one note of caution - if you have duplicate costs,
you'll get the same Acumul Value for each record with the
same cost. If you have a number field for sorting
records, then you can get accurate results. If your table
looked like this:
RecordID Description Cost
1 Red Truck $8.00
2 Blue Truck $7.00
3 Green Truck $7.00
4 Purple Truck $6.00
then the query, qryRunningSum_Correct, should look like
this:

SELECT T1.RecordID, T1.Description, Max(T1.Cost) AS Cost,
(SELECT SUM(T2.Cost) FROM tblMyDollarTable AS T2 WHERE
T2.recordID<=T1.RecordID) AS [Acumul Value]
FROM tblMyDollarTable AS T1
GROUP BY T1.RecordID, T1.Description;

which will return this:

RecordID Description Cost Acumul Value
1 Red Truck $8.00 $8.00
2 Blue Truck $7.00 $15.00
3 Green Truck $7.00 $22.00
4 Purple Truck $6.00 $28.00

The first query, qryRunningSum_Simple, returns this:
Description Cost Acumul Value
Red Truck $8.00 $8.00
Blue Truck $7.00 $22.00
Green Truck $7.00 $22.00
Purple Truck $6.00 $28.00

Both Blue Truck and Green Truck have the same Acumul
Value, the one thet belongs to the later (the last) of
same value records.

I hope this helped.

:-)
 
Back
Top