Cumulative Addition in Query?

[gkrenton]

I'm trying to create rolling weekly onhand numbers.
I've gotten week 1 figured out but am now trying to figure out how to calculate the inventory position for week 2 by adding data to week 1.

I have 2 tables being pulled used:

PYactuals which has the weekly data
BOM which has the beg year starting data.

This get's me my starting point
BegData: IIf([week]=1,[SumOfhb_beg_retl]+[SumOfRetailOnHand],

But it's the 'else' part of this statement that I'm stumped on.

Reading through other threads I'm thinking dlookup could be the solution? But I'm not clear on the syntax & my guesses have errored out.

my gut thinks it should be
BegData: IIf([week]=1,[SumOfhb_beg_retl]+[SumOfRetailOnHand],dlookup([SumOfRetailOnhand],pyactuals, [week]+1)

I've never used Dlookup before so not really sure it's the right solution. Any help would be appreciated.

Thanks -
Gina

 

[SBerthold]

I think what you are looking for to use is the DSum() function

 

[gkrenton]

Hmm, not familiar with dsum but when I put dsum in the 'else' statement the numbers go insanely huge.

I got this dlookup statement to sort of work EXCEPT it's adding the 'negative' week1 # to the cum vs subtracting

BegData: IIf([week]=1,[SumOfhb_beg_retl]+[SumOfRetailOnHand],DLookUp([SumOfhb_beg_retl]+[SumOfRetailOnHand],"PyActuals",[week]+1))

BegOnHand = 51905
week1 = -1096.58
Total 50808.45

But week 2 results in
52845 which is
BegOnHand + 1096.58(week1) + week2 940.69

Instead of the proper cum result of
51905-1096.58+940.69 = 51749.14.

I'm confused as to why the math of a negative is being added in the dlookup function as listed above.
The dsum() seems to do some math in a way that I do not get.

Any more suggestions?

gina

 

[SBerthold]

i´nstead of just "putting" it in, look it up under Access Help and in the MS knowledge base:

http://support.microsoft.com/kb/290136

 

[gkrenton]

Thanks I've been reading the ms knowledge bases all friggin day before I made this post. Guess I must be 'slow' but when I use dsum in this query the results INSTEAD of
getting 51749 I'm getting 126618345 using this syntax

BegData2: DSum([SumOfRetailOnHand]+[SumOfhb_beg_retl],"PyActuals",[week]+1)

Thus I'm stumped & am nicely asking for some assistance to help me get pointed in the right direction since for whatever reason I'm not getting there with my own comprehension level.

- gina

 

[SBerthold]

I thing it's adding the starting value each time. You need to try it first with-out adding it and see what happens, then maybe even download the sample in that link and try that to see how it is working.

Once you get it working, you need to then figure out how to add the starting value to the first week. One idea would be to first create an UNION ALL query to pull the data from the two tables together, and then base your cumulative query on that.

 

[MICKI0220]

I need to some assistance in what I believe is a query using DSUM. I have to create a Bonus report. The report will show what the negative carryover is. For example. Each month a worker gets a bonus based on saved hours. It is possible for them to get a negative bonus at which point this goes into the negative carryover field. This is not a problem for the first month. However they must now work themselves out of the negative. So, If they get a bonus the next month, the allocated bonus field will be populated with the bonus, but the negative carryover must now subtract the bonus or add to the negative if they still didn't earn a bonus. Once they have paid all the negative back by earned bonus, the negative should show zero instead of - a number. Please point me in the right direction. I have looked at the DSUM in the knowledge base and I don't get it.