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,

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 wrote:
> 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,

On 27 Jan, 13:59, John Spencer <spen...@chpdm.edu> wrote:
> 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? =A0Is Cleared another Date field? =
=A0If so
> how are the recieved field and the cleared related to each other? =A0Or i=
s there
> a relationship.
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
>
>
> NPell wrote:
> > 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,- Hide quoted text -
>
> - 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    =A3200            6   =A3150              6   =A350
02/01/2010     10    =A3400            3   =A3250            13   =A3200
03/01/2010       4    =A3100           10   =A3150            7   =A3150

Does this help a bit more??

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

NPell wrote:
> On 27 Jan, 13:59, John Spencer <spen...@chpdm.edu> wrote:
>> 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 wrote:
>>> 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,- Hide quoted text -
>> - 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??