Link to home
Start Free TrialLog in
Avatar of jay_blake
jay_blake

asked on

Running Total in a Single Query

Can anyone tell me how to create a "running total" calculated column in a single query?  Basically, I have one query containing 1 table.  In that same query, I have a value column.  I need to create a running total column for my single value column.  I know how to do this in a report, but I don't want a report.  Here's the query.  As you can see it's very simple.  I'm trying to do a running total on the "Percent" column.

SELECT qryCostbyItem.ITEM, qryCostbyItem.ITEMDESCRI, qryCostbyItem.EXTRADESCR, qryCostbyItem.SumOfValue, [qryCostbyItem].[SumOfValue]/(SELECT qryTotalSample.TotalSample FROM qryTotalSample) AS [Percent]
FROM qryCostbyItem
GROUP BY qryCostbyItem.ITEM, qryCostbyItem.ITEMDESCRI, qryCostbyItem.EXTRADESCR, qryCostbyItem.SumOfValue, [qryCostbyItem].[SumOfValue]/(SELECT qryTotalSample.TotalSample FROM qryTotalSample);
Avatar of GRayL
GRayL
Flag of Canada image

SELECT a.pk, a.fldVal, (SELECT Sum(b.fldVal) FROM myTable b WHERE b.pk = a.pk AND b.fldVal <= a.fldVal) AS RunningTotal FROM myTable a
Avatar of jay_blake
jay_blake

ASKER

What is "SELECT a.pk"?
Oh!  'Primary Key'.....gotcha.  Let me test it.
Sorry, I get too cryptic at times. a and b are of course aliases of the same table.
This is what I've got, and it just gives me the same total as is in my table.

SELECT qrySumCostByItem_A.ITEM, qrySumCostByItem_A.percent (SELECT Sum(qrySumCostByItem_B.percent) FROM qrySumCostByItem_B WHERE qrySumCostByItem_B.Item = qrySumCostByItem_A.Item AND qrySumCostByItem_B.value <= qrySumCostByItem_A.value) AS RunningTotal FROM qrySumCostByItem_A;
Sorry, I meant this:

SELECT qrySumCostByItem_A.ITEM, qrySumCostByItem_A.percent (SELECT Sum(qrySumCostByItem_B.percent) FROM qrySumCostByItem_B WHERE qrySumCostByItem_B.Item = qrySumCostByItem_A.Item AND qrySumCostByItem_B.percent <= qrySumCostByItem_A.percent) AS RunningTotal FROM qrySumCostByItem_A;
I'll assume the queryin the question is saved as qryPct.  You should have written:

SELECT a.ITEM, a.percent (SELECT Sum(b.percent) FROM qryPct b WHERE b.Item = a.Item AND b.percent <= a.percent) AS RunningTotal FROM qryPct a;
Aside from the fact that you used a different table name I don't see what is different between what you wrote and what I wrote.  What am I missing?
If I make the b.item and the a.item not equal ( <> ), then it does give me a running total, but my top number is only 92.88%, which is the difference between the top-rated number and 100.  If I make the items equal, I get only the percent for that item--not a running total.  So I think the item number is supposed to be <>, but there's something else missing.
ASKER CERTIFIED SOLUTION
Avatar of jay_blake
jay_blake

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This was contained in an Email I received notifying me the question would be closed in 4 days:

Question: Running Total in a Single Q...
Zone: Microsoft Access Database
Reason: I would like to aware GrayL 250 pts since the solution he or she provided was 50% of my solution.  I came up with the other 50%.