Running Sum

[NPell]

Hello,

I have been looking at some running sum examples (http://
support.microsoft.com/kb/290136), but can not work out how to apply it
to my query.

I have Recieved, Recieved Amount .. then Cleared, Cleared Amount .. i
am trying to work out a running backlog of work.

So
A: Recieved, Rec Amount .. Cleared, Clrd Amount .. Backlog, Backlog
Amount.
B: Recieved, Rec Amount .. Cleared, Clrd Amount .. Backlog of A -
Cleared + Received.
C: Recieved, Rec Amount .. Cleared, Clrd Amount .. Backlog of B -
Cleared + Received.
and so on?

Im guessing its a DSUM thing, but i cant make the connection.

Appreciate any help.
Regards,

 

[John Spencer]

Would you care to expand on your field types and explain how you want to
calculate Backlog and Backlog Amount?

Perhaps a couple rows of sample data would help us to figure out what you are
trying to do.

For instance is received a Date field? Is Cleared another Date field? If so
how are the recieved field and the cleared related to each other? Or is there
a relationship.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[NPell]

Would you care to expand on your field types and explain how you want to
calculate Backlog and Backlog Amount?

Perhaps a couple rows of sample data would help us to figure out what youare
trying to do.

For instance is received a Date field?  Is Cleared another Date field?  If so
how are the recieved field and the cleared related to each other?  Or is there
a relationship.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County









- Show quoted text -

Sorry, this is on a date by date basis and the cleared / recieved
entries are numbers.

Recieved Cleared Backlog/
Outstanding
01/01/2010 12 £200 6 £150 6 £50
02/01/2010 10 £400 3 £250 13 £200
03/01/2010 4 £100 10 £150 7 £150

Does this help a bit more??

 

[John Spencer]

PERHAPS something like the following SQL statement. Obviously you need to
used your field and table names.

SELECT A.[TheDate], A.[Received], A.[AmountReceived], A.[Cleared],
A.[AmountCleared]
, Sum(B.[Received]) - Sum(B.[Cleared]) as Backlog
, Sum(B.[AmountReceived]) - Sum(B.[AmountCleared]) as BackLogAmount
FROM [TheTable] as A INNER JOIN [TheTable] As B
ON A.[TheDate] >= B.[TheDate]
GROUP BY A.[TheDate], A.[Received], A.[AmountRecieved], A.[Cleared],
A.[AmountCleared]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County