Access Query

Hi

I am doing an running total in a query from a Table
Fields of the Table are
1. StageID,
2.Month Year
3.Field Name
4.Field Value
SELECT Compliance.[StageID], Compliance.[MonthYear], Compliance.[Field Name], Compliance.[Field Value], DSum("[Field Value]","[Compliance]","StageID <" & [StageID]) AS expr1
FROM Compliance
WHERE (((Compliance.MonthYear) Between [sd] And [ed]) And ((Compliance.[Field Name])="Total Afd CPE Transactioned"));

Open in new window


The problem here is as doing running total for all other fields, not just for "Total Afd CPE Transactioned"
surah79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
so, are you trying to get the running total grouped by StageID, [Field Name], and [MonthYear]?

Can you do this in a report instead of a query?

If so, create a report based on your query or table ([Compliance]), then add groupings by StageID and [Field Name], then sort by [MonthYear], then add two textboxes with the [Field Value] as the control source.  In the 2nd textbox, set the "Running Sum" property (on the data tab of the Properties Dialog) to "Over Group"

If you need it in the query, the way I generally do it is something like:

SELECT C1.[StageID], C1.[Field Name], C1.[MonthYear], C1.[Field Value], Sum(C2.[Field Value] as Running_Sum
FROM Compliance as C1
LEFT JOIN Complaince as C2
ON C1.[StageID] = C2.[StageID]
AND C1.[Field Name] = C2.[Field Name]
AND C1.[MonthYear] >= C2.[MonthYear]
WHERE C1.[MonthYear] BETWEEN [sd] AND [ed]
GROUP BY C1.[StageID], C1.[Field Name], C1.[MonthYear]
0
surah79Author Commented:
Hi Fyed

I can't do this in the report, I am triying to run your query its giving me the Syntax error "Missing operator"
0
surah79Author Commented:
Hi fyed

Now its giving me the error
"you tried to execute a query that does not include the specified expression 'Field value' as a part of aggregate function.
SELECT C1.[StageID], C1.[Field Name], C1.[MonthYear], C1.[Field Value], Sum(C2.[Field Value]) as Running_Sum
FROM Compliance as C1
LEFT JOIN Compliance as C2
ON C1.[StageID] = C2.[StageID] 
AND C1.[Field Name] = C2.[Field Name]
AND C1.[MonthYear] >= C2.[MonthYear]
WHERE C1.[MonthYear] BETWEEN [sd] AND [ed]
GROUP BY C1.[StageID], C1.[Field Name], C1.[MonthYear] 

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

hnasrCommented:
Check this EE solution:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24288331.html

Applying to your question, repeat the (Select sum ) expression

SELECT a.[StageID]
           , a.[MonthYear]
           , a.[Field Name]
           , a.[Field Value]
           , (Select Sum(b.Field1) From Compliance b Where b.StageID<=a.StageID) As totalField1, 
           , (Select Sum(c.Field2) From Compliance c Where c.StageID<=a.StageID) As totalField2, 
FROM Compliance As a
WHERE ( (a.MonthYear) Between [sd] And [ed] );

Open in new window

0
surah79Author Commented:
Hi Hnasr,

I am getting an error while running your query

"The SELECT statement includes a reserved word or an argument name that is mispelled or missing or punctutation is incorrect"
0
hnasrCommented:
Type here the sql you used. Did you use the proper field names as in your table.

Try to reproduce the issue, by creating a database with just the table and the mentioned query, and upload.
0
Dale FyeCommented:
surah,

I forgot to add the [Field Value] to the group by:

GROUP BY C1.[StageID], C1.[Field Name], C1.[MonthYear], C1.[Field Value]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.