Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Running Sum Query With Add and Minus Value

Status
Not open for further replies.

Beejai66

Technical User
Mar 23, 2003
7
AU
Hi

Referring to "How to get a Running Sum in Queries" faq701-5268

I need assistance to get a running sum on my query, but with a twist. I need to have the running sum add or minus the value based on criteria of another field. The running sum will be used to produce a bar chart. Here's my code minus my attempt at the running sum:

SELECT qry_YellowTags_ChartData.[Outlook Date], Count(qry_YellowTags_ChartTagStatus.[Tag Status]) AS TagCount, qry_YellowTags_ChartTagStatus.[Tag Status], qry_YellowTags_ChartTagStatus.ReleaseStatus, IIf([ReleaseStatus]="Active","Add","Minus") AS AddMinus
FROM qry_YellowTags_ChartTagStatus RIGHT JOIN qry_YellowTags_ChartData ON qry_YellowTags_ChartTagStatus.[Tag No] = qry_YellowTags_ChartData.[Tag No]
GROUP BY qry_YellowTags_ChartData.[Outlook Date], qry_YellowTags_ChartTagStatus.[Tag Status], qry_YellowTags_ChartTagStatus.ReleaseStatus, IIf([ReleaseStatus]="Active","Add","Minus")
ORDER BY qry_YellowTags_ChartData.[Outlook Date];

Thanks in advance for your assistance. BJ
 
I'm not sure what field you are trying to sum, but what I would do is something like:

SUM(Iif(releaseStatus = "Active", ValueToAdd, (ValueToAdd*-1)))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design
 
Oops sorry - a piece of vital information missed out. I want to get a running sum on the Tag Count, and if it is Active then 'add' the value, but if the tag is Completed, to 'minus' the count value. Hope this is clear enough. Thanks, BJ.
 
Ok, did you take the example and replace ValueToAdd with [Tag Count] and see what happens?
 
Sorry meant to say I was trying your solution. Unfortunately I get the following error: "Cannot have aggregate function in GROUP BY clause Sum(IIf([releaseStatus]="Active",[TagCount],([TagCount]*-1)))
 
you don't want to group by that, only non-aggregate fields. Can you post the new SQL with the SUM function and we'll have a look?

leslie
 
Here's the code as requested. Thanks, BJ

SELECT qry_YellowTags_ChartData.[Outlook Date], Count(qry_YellowTags_ChartTagStatus.[Tag Status]) AS TagCount, qry_YellowTags_ChartTagStatus.[Tag Status], qry_YellowTags_ChartTagStatus.ReleaseStatus, IIf([ReleaseStatus]="Active","Add","Minus") AS AddMinus, Sum(IIf([ReleaseStatus]="Active",[TagCount],([TagCount]*-1))) AS RunTotal
FROM qry_YellowTags_ChartTagStatus RIGHT JOIN qry_YellowTags_ChartData ON qry_YellowTags_ChartTagStatus.[Tag No] = qry_YellowTags_ChartData.[Tag No]
GROUP BY qry_YellowTags_ChartData.[Outlook Date], qry_YellowTags_ChartTagStatus.[Tag Status], qry_YellowTags_ChartTagStatus.ReleaseStatus, IIf([ReleaseStatus]="Active","Add","Minus"), Sum(IIf([ReleaseStatus]="Active",[TagCount],([TagCount]*-1)))
ORDER BY qry_YellowTags_ChartData.[Outlook Date];
 
[tt]SELECT
qry_YellowTags_ChartData.[Outlook Date],
Count(qry_YellowTags_ChartTagStatus.[Tag Status]) AS TagCount,
qry_YellowTags_ChartTagStatus.[Tag Status],
qry_YellowTags_ChartTagStatus.ReleaseStatus,
IIf([ReleaseStatus]="Active","Add","Minus") AS AddMinus,
Sum(IIf([ReleaseStatus]="Active",[TagCount],([TagCount]*-1))) AS RunTotal
FROM qry_YellowTags_ChartTagStatus
RIGHT JOIN qry_YellowTags_ChartData ON qry_YellowTags_ChartTagStatus.[Tag No] = qry_YellowTags_ChartData.[Tag No]

GROUP BY qry_YellowTags_ChartData.[Outlook Date], qry_YellowTags_ChartTagStatus.[Tag Status], qry_YellowTags_ChartTagStatus.ReleaseStatus, IIf([ReleaseStatus]="Active","Add","Minus")

ORDER BY qry_YellowTags_ChartData.[Outlook Date];[/tt]

only include fields that do not use an aggregate function in your GROUP BY Clause. In this case all the fields in the SELECT that don't have COUNT or SUM.
 
Thanks but it now gives the following error: "Subqueries cannot be used in the expression (IIf(IIf(Not [Final Release Date]Is Null, "Completed", "Active")="Active",[TagCount],([TagCount]*-1)))"
I'll have to leave it for tonight and check for any follow up, am keeping eyelids open with match-sticks at 1 am. BJ
 
Ok, I'll be around tomorrow morning if you want additional help. I'm not real sure what you are trying to accomplish with the statement you just wrote, but it looks like you are trying to do the sum if FinalReleaseDate is not null?

Leslie
 
Hi. I'm trying to get a running total in my query as per the example below. So dependant upon the Release Status it will either add the value to the RunTotal Sum or Minus the value from the RunTotal. I hope this is clearer.

Tag No Outlook Date Tag Status Final Release Date Release Status Tag Count RunTotal
2059 29/05/2006 Overdue Active 1 1
11843 23/05/2006 Overdue Active 1 2
10618 30/06/2006 Open 04/05/02006 Completed 1 1

I was trying to find a way to do this using AceMan1 example as in (FAQ701-5268) but with the twist of being able to add or minus the value to the running sum. I hope this is clear.

I have created a new query so that it does not group the tags on a particular day, I can do that later. So I've got the query showing all my records. Here's my new code:
SELECT tblYellowTags.[Tag No], tblYellowTags.[Outlook Date], tblYellowTags.[Final Release Date], IIf([Outlook date]<=Now(),"Overdue","Open") AS [Tag Status], IIf([Final Release Date] Is Not Null,"Completed","Active") AS ReleaseStatus, Count([Tag Status]) AS TagCount, Sum(IIf([ReleaseStatus]="Active",[TagCount],([TagCount]*-1))) AS RunTot
FROM tblPlantActive INNER JOIN tblYellowTags ON tblPlantActive.PlantDescription = tblYellowTags.Plant
GROUP BY tblYellowTags.[Tag No], tblYellowTags.Ownership, tblYellowTags.Plant, tblYellowTags.[Tag Originator], tblYellowTags.[Outlook Date], tblYellowTags.[Final Release Date], IIf([Outlook date]<=Now(),"Overdue","Open"), IIf([Final Release Date] Is Not Null,"Completed","Active");

Thanks BJ
 
How are ya Beejai66 . . .

Sorry to get to ya so late . . .

The [blue]Running Sum[/blue] technique was not meant to be used in an [blue]Totals Query![/blue] Its meant to be used in a simple [blue]Select[/blue] query where a running sum is needed.

I havn't tackled it yet, but you need the equivalent of a subquery comprising your SQL and a main query that performs the running sum . . .

I hope this is clear! . . .



Calvin.gif
See Ya! . . . . . .
 
Thanks, Your input clarified things. I was able to get the running sum working with Add and Minus (using negative numbers for minus) which worked for my requirements. I used the Northwind database example as well as your posting to help me 'get my head' around it, and get my own code working.

Thanks & Regards, BJ.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top