running total query 1

[hnunez4399]

I want to create a query that will have a running total based on another field that is already have a value.
How or what would be the easiest way without using a report.

Example

Field 1 running total field
10% 10%
20% 30%
30% 60%
40% 100%

Any help would be apreciated.
Thx,

 

[dhookom]

This is near impossible without another field that identifies a sorting order. You are only showing one Field with your calculated column. You need to identify another field that describes the order.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hnunez4399]

ok so how abot the autonumber that is generated during the import process. Each record in the file is unique.

autonum Field 1 running total field
1 10% 10%
2 20% 30%
3 30% 60%
4 40% 100%

 

[dhookom]

Try:

SELECT AutoNum, [Field 1], (SELECT Sum([Field 1]) FROM tblA A WHERE A.AutoNum<=tblA.AutoNum) as RunningTotalField
FROM tblA;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hnunez4399]

Do I put this in the SQL window for the query?

 

[dhookom]

Yes, however you need to change the field and table names to match your field and table names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hnunez4399]

what if my autonumer field is actually a number? do I just replace autonumber with number?

 

[hnunez4399]

I am confused on the part of...

FROM tblA A WHERE A.AutoNum<=tblA.AutoNum


what would be the tblA??

and then it starts again with....A where

 

[dhookom]

Why don't you just share your table name and 2 field names?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Melagan]

Star for Duane - I've been following this thread and am quite impressed with the results. Nice SQL there.

~Melagan
______
"It's never too late to become what you might have been.

 

[hnunez4399]

sure....

My table name = zzz_FonABC2
my unique Field = rec#
Field I am trying to do a running total on is = %Activity

If possible, I would like to populate the running total into a field called = Cum Activity

Let me know if this helps...
Sorry am still learning.

 

[dhookom]

Try this in your SQL view:

SELECT [Rec#], [%Activity], (SELECT Sum([%Activity]) FROM zzz_FonABC2 A WHERE A.[Rec#]<=zzz_FonABC2.[Rec#]) as [Cum Activity]
FROM zzz_FonABC2
ORDER BY [Rec#];

BTW: You should consider finding and using a naming convention that does allow spaces or symbols in table or field names.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hnunez4399]

I get an error that says...

It can't find input table or query tbla

 

[dhookom]

There is no "tbla" in the sql view that I just posted. If your field and table names are accurate, you only need to open the query in sql view and paste the above sql statement.

Next time you get an error with a query, please post your current sql view so that someone can help you.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hnunez4399]

Thank you very much it works!! I must have pasted the code wrong yesterday...

 

[PHV]

Another way:
SELECT A.[Rec#], A.[%Activity], Sum(B.[%Activity]) AS [ running total]
FROM zzz_FonABC2 AS A INNER JOIN zzz_FonABC2 AS B ON A.[Rec#] >= B.[Rec#]
GROUP BY A.[Rec#], A.[%Activity]
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886