running sum query

[Guest]

I know there have been a number of posts on this topic already, but I can't
seem to get any of the solutions to work for me (my Access/SQL skills are
pretty bad!)

I have a query called [qry123] that contains:

Item Date Change InvLvl <- These are the values I need
from Access
A 3/1/2004 5 5
A 4/1/2004 -2 3
A 6/1/2004 3 6
B 3/1/2004 2 2
B 8/1/2004 -1 1
etc...

Change = the net amount of qty received - qty shipped over a particular month
InvLvl = Running sum of Change per item (basically will be my inventory
level for the month)

I've had qry123 sort my result by Item, then by Month.

Please help...

 

[Guest]

Hi Robert,

Try this query. It should do the trick. Table name is "Table1" with fields
"Item", "ADate", and "Change". The "InvLvl" is field created when the query
below is executed.

SELECT Table1.Item, Table1.ADate, Table1.Change,
DSum("Change","Table1","Item='" & [Item] & "' AND ADate<=#" & [ADate] & "#")
AS InvLvl
FROM Table1;

 

[Guest]

Wow, it works! Thanks! The only problem is that its uber slow...<sigh>, but
still faster than doing it by hand.

Hi Robert,

Try this query. It should do the trick. Table name is "Table1" with fields
"Item", "ADate", and "Change". The "InvLvl" is field created when the query
below is executed.

SELECT Table1.Item, Table1.ADate, Table1.Change,
DSum("Change","Table1","Item='" & [Item] & "' AND ADate<=#" & [ADate] & "#")
AS InvLvl
FROM Table1;

I know there have been a number of posts on this topic already, but I can't
seem to get any of the solutions to work for me (my Access/SQL skills are
pretty bad!)

I have a query called [qry123] that contains:

Item Date Change InvLvl <- These are the values I need
from Access
A 3/1/2004 5 5
A 4/1/2004 -2 3
A 6/1/2004 3 6
B 3/1/2004 2 2
B 8/1/2004 -1 1
etc...

Change = the net amount of qty received - qty shipped over a particular month
InvLvl = Running sum of Change per item (basically will be my inventory
level for the month)

I've had qry123 sort my result by Item, then by Month.

Please help...

 

[Guest]

Hi Robert,

The slow usually cause by 3 things.

1) Hardware speed (don't need to say anything about that).

2) No indexes (need index on Item and ADate).

3) Huge table with lots data (Will hopefully the index will take care of that.

One more thing. The query only works if the primary key is on "Item" and
"ADate".

Wow, it works! Thanks! The only problem is that its uber slow...<sigh>, but
still faster than doing it by hand.

Hi Robert,

Try this query. It should do the trick. Table name is "Table1" with fields
"Item", "ADate", and "Change". The "InvLvl" is field created when the query
below is executed.

SELECT Table1.Item, Table1.ADate, Table1.Change,
DSum("Change","Table1","Item='" & [Item] & "' AND ADate<=#" & [ADate] & "#")
AS InvLvl
FROM Table1;

I know there have been a number of posts on this topic already, but I can't
seem to get any of the solutions to work for me (my Access/SQL skills are
pretty bad!)

I have a query called [qry123] that contains:

Item Date Change InvLvl <- These are the values I need
from Access
A 3/1/2004 5 5
A 4/1/2004 -2 3
A 6/1/2004 3 6
B 3/1/2004 2 2
B 8/1/2004 -1 1
etc...

Change = the net amount of qty received - qty shipped over a particular month
InvLvl = Running sum of Change per item (basically will be my inventory
level for the month)

I've had qry123 sort my result by Item, then by Month.

Please help...