Running Total in Access 2003

[Jasper Recto]

I have a query that has 4 columns

Date, Qty, UnitPrice and Amount. The amount is a formula that multiplies
Qty x UnitPrice.

I would like a 5th column that gives me the running total for each line.

So the first line is the first amount. The second line is the second amount
plus the first amount. The 3rd line is the 2nd line total plus the 3rd
amount, and so on.

Is this possible?

Also, I need it to reset itself after each year time frame. Is this
possible?

Thanks!
Jasper

 

[Michel Walsh]

I assume there is no duplicated dates values (in fact, no Date_TIME
duplicated values), so:

SELECT a.date,
LAST(a.qty) As qtyForThisDate,
LAST(a.unitPrice) AS unitPriceForThisDate,
LAST(a.qty)*LAST(a.unitPrice) AS AmountForThisDate,
SUM(b.qty*b.unitPrice) AS runningTotalUpToThisDate
FROM myTableNameHere AS a INNER JOIN myTableNameHere AS b
ON a.date >= b.date
GROUP BY a.date




Basically, we define two 'references' to the table, reference_b car 'run'
on all records as long as

reference_a.date >= reference_b.date


is respected.

Clearly, then, with

GROUP made by reference_a.date

then, for a given reference_a.date, SUM( b.something) would sum over all
records having a date less than, or equal to, the one in reference_a.date.


I used LAST to reach other fields. Alternatively, someone can use:



SELECT a.date,
a.qty As qtyForThisDate,
a.unitPrice AS unitPriceForThisDate,
a.qty*a.unitPrice AS AmountForThisDate,
SUM(b.qty*b.unitPrice) AS runningTotalUpToThisDate
FROM myTableNameHere AS a INNER JOIN myTableNameHere AS b
ON a.date >= b.date
GROUP BY a.date, a.qty, a.unitPrice


since it is a rule: a field in SELECT must either be aggregated ( as with
SUB(b.qty), or LAST(a.qty), even LAST( a.qty * a.unitPrice) will be
acceptable ) , either the field must be in the GROUP BY clause.

The problem is that adding un-needed fields in the GROUP BY clause, that
have the tendency to slow down the execution time. And here, the date field
(seems to be) is enough to define the groups.



Vanderghast, Access MVP

 

[Allen Browne]

No. It is not possible to give a running sum in Access with just the 4
fields you have below.

If you had a primary key named (say) ID, and the query is sorted by this
field, and you don't need to cope with the possibility that the user may
sort or filter it differently, you could use a subquery or a DSum()
expression to get the running sum.

SELECT ID, [Date], Qty, UnitPrice, Amount,
(SELECT Sum([Qty] * [UnitPrice]) AS ProgTotal
FROM Table1 AS Dupe
WHERE Dupe.ID <= Table1.ID) AS HowMuchSoFar
FROM Table1
ORDER BY ID;

The DSum() would be much slower, but the other fields in the query would be
editable.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html#YTD

 

[Wayne-I-M]

"very simple method"

Column 1
[TableName]![NameOfField_1]

Column 2
[TableName]![NameOfField_1] + [TableName]![NameOfField_2]


Column 3
[TableName]![NameOfField_1] + [TableName]![NameOfField_2] +
[TableName]![NameOfField_3]

Column 4
[TableName]![NameOfField_1] + [TableName]![NameOfField_2] +
[TableName]![NameOfField_3] + [TableName]![NameOfField_4]


Column 5
[TableName]![NameOfField_1] + [TableName]![NameOfField_2] +
[TableName]![NameOfField_3] + [TableName]![NameOfField_4] +
[TableName]![NameOfField_5]

Can't get any simpler than that. Even though there are more elegant methods
- this will work everytime.

eg
You can reference the proceeding record but this has a few problems if there
are spaces in the ID field (or other field referenced).

 

[Wayne-I-M]

ooops (again) missed the "running" from you post - ignor my answer. It does
not "run"



--
Wayne
Manchester, England.

"very simple method"

Column 1
[TableName]![NameOfField_1]

Column 2
[TableName]![NameOfField_1] + [TableName]![NameOfField_2]


Column 3
[TableName]![NameOfField_1] + [TableName]![NameOfField_2] +
[TableName]![NameOfField_3]

Column 4
[TableName]![NameOfField_1] + [TableName]![NameOfField_2] +
[TableName]![NameOfField_3] + [TableName]![NameOfField_4]


Column 5
[TableName]![NameOfField_1] + [TableName]![NameOfField_2] +
[TableName]![NameOfField_3] + [TableName]![NameOfField_4] +
[TableName]![NameOfField_5]

Can't get any simpler than that. Even though there are more elegant methods
- this will work everytime.

eg
You can reference the proceeding record but this has a few problems if there
are spaces in the ID field (or other field referenced).



--
Wayne
Manchester, England.

I have a query that has 4 columns

Date, Qty, UnitPrice and Amount. The amount is a formula that multiplies
Qty x UnitPrice.

I would like a 5th column that gives me the running total for each line.

So the first line is the first amount. The second line is the second amount
plus the first amount. The 3rd line is the 2nd line total plus the 3rd
amount, and so on.

Is this possible?

Also, I need it to reset itself after each year time frame. Is this
possible?

Thanks!
Jasper