Running Totals based on groups 1

[Costefran]

I would like to run a query of spend as a running total sum against budgets but not sure how to approach this

the resulting recordset would look something like this


Order Project Value of Running
Number Project Budget this Total Sum
(unique number) Number Value order of Orders
-----------------------------------------------------------
1 PN100 £20000 £100 £100
2 PN100 £20000 £200 £300
3 PN100 £20000 £300 £600
4 PN100 £20000 £400 £10000
5 PN101 £10000 £10 £10
6 PN101 £10000 £20 £30


As you can see running sums are total by project number groups

I have wracked my brains trying to work this out so any help would save my continuing headache

I have looked at Dsum but not sure of the code syntax etc

As noted, any help would be appreciated

 

[Remou]

Have you looked at the Running Sum property for textboxes? One option is Over Group, but you will need to group your report.

 

[Costefran]

Remou

Thanks
Could I do this with Dsum (although not sure of syntax)

 

[PHV]

As whe are in the JetSQL forum, here a pure SQL suggestion:

SELECT A.[Order Number], A.[Project Number], A.[Project budget], A.[Order Value], Sum(B.[Order Value]) AS runningSum
FROM yourTable AS A INNER JOIN yourTable AS B ON A.[Project Number]=B.[Project Number] AND A.[Order Number]>=B.[Order Number]
GROUP BY A.[Order Number], A.[Project Number], A.[Project budget], A.[Order Value]
ORDER BY 2, 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Costefran]

Thanks will give this a go

 

[Remou]

I thought I was in the reports forum.

 

[Costefran]

PHV

What a genius you are - this is brilliant and works!

Many thanks - you have saved me hours more headaches

 

[dhookom]

A star for PH. I generally have trouble figuring out that particular syntax. My kludgy syntax usually involves a DCount() or a subquery.

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

hmmmmmmmmmmmmmmmmm ...

I have already learned a bit here, specifically, that while Jet cannot 'represent' (e.g. show in the grid view) the unequal join (A.{field] >= b.Field), it DOES accept the expression as long as it is created in hte SQL view and shown in the recordset view.

well I have to consult the oracle of SQL!!

I attempted to adapt your soloution ...

SELECT A.YrMnth, A.PhaseNm, A.PWE, Sum(B.PWE) AS PWE_Sum
FROM qryRothLabor AS A INNER JOIN qryRothLabor AS B ON (A.YrMnth = B.YrMnth) AND (A.PhaseNm >= B.PhaseNm)
GROUP BY A.YrMnth, A.PhaseNm, A.PWE
ORDER BY A.YrMnth, A.PhaseNm;

my results are far from the desired:

YrMnth	PhaseNm	PWE	PWE_Sum
2006-05	FE		
2006-08	PM		
2006-09	PM		
2006-10	PM		
2006-11	PM		
2006-12	PM		
2007-01	FE		
2007-01	PM		
2007-01	PWE	85	85
2007-02	FE		
2007-02	PM		
2007-02	PWE	1657.5	1657.5
2007-03	FE		
2007-03	PM		
2007-03	PWE	4687.5	4687.5
2007-04	FE		
2007-04	PM		
2007-04	PW		
2007-04	PWE	361.25	361.25
2007-05	FE		
2007-05	PDW		
2007-05	PM		
2007-05	PW		
2007-05	PWE	701.25	701.25

as the sqmple sadly indicates

Mayhap you can help educate me? Sir PHV!




MichaelRed

 

[PHV]

my results are far from the desired
could you please post the expected results ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886