If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Running Total in Access 2003
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 |
#2
|
|||
|
|||
Running Total in Access 2003
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 "Jasper Recto" wrote in message ... 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 |
#3
|
|||
|
|||
Running Total in Access 2003
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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jasper Recto" wrote in message ... 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 |
#4
|
|||
|
|||
Running Total in Access 2003
"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. "Jasper Recto" wrote: 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 |
#5
|
|||
|
|||
Running Total in Access 2003
ooops (again) missed the "running" from you post - ignor my answer. It does
not "run" :-) -- Wayne Manchester, England. "Wayne-I-M" wrote: "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. "Jasper Recto" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|