Multiple Critera in Running Total Query (DSum I think)

[jversiz via AccessMonster.com]

Hello,

I am trying to make the DSum work for me, but I am not an expert. I have
read many posts and still cannot get the DSum to show what I want.

I have a table (tblQty) that has 4 fields: Item, Whse, Week, and Quantity.
I am trying to create a running total field by week, grouped by item,
warehouse, and week (as shown below)

Item Whse Week Qty RunningTotal
123 A 1 10 10
123 B 1 5 5
123 A 2 5 15
123 B 2 20 25
456 A 1 7 7
456 B 1 6 6
456 A 2 5 12
456 B 2 3 9

Here is what I have so far...

RunningTotal: DSum("Qty","tblQty","[Item] = Item AND [Whse] = Whse AND [Week]
<= Week")

Instead of a running total, I get a total of everything in the table, which
is 61 in my example above.

Any help would be greatly appreciated!

Best Regards,
James C.

 

[Dale Fye]

James,

You have the right idea, just not the details. Assuming that your Item
field is numeric, then try this:

RunningTotal: DSUM("Qty", "tbl_Qty", "[Item] = " & [Item] & " AND [Whse] =
'" & [Whse] & "' AND [Week] = " & [Week])

Another way to do this is with an non-Equi join. You are used to creating
joins that look like:

SELECT A.SomeField, B.SomeField
FROM A INNER JOIN B ON A.JoinField = B.JoinField

Well, you can also create non-equal joins, like the following. The down
side of this is that you have to create this type of join in the SQL view.
I generally create the basic query and create a equi-join (in this case on
all three fields). Then I test it to make sure it is doing the equi-join
properly, then I go to the SQL view, modify the join and run the query
again.

SELECT A.Item, A.Whse, A.Week, A.Qty, Sum(B.Qty) as RunningSum
FROM yourTable AS A LEFT JOIN yourTable AS B
ON A.Item = B.Item
AND A.Whse = B.Whse
AND A.Week >= B.Week
GROUP BY A.Item, A.Whse, A.Week, A.Qty

You can test out both methods to determine which runs quicker in your
application, my guess is that it will be the latter.

Dale

 

[jversiz via AccessMonster.com]

Hi Dale,

Both my Item and Whse fields are alpha-numeric. Would that eliminate option
one for me?

Regards,
James C.

James,

You have the right idea, just not the details. Assuming that your Item
field is numeric, then try this:

RunningTotal: DSUM("Qty", "tbl_Qty", "[Item] = " & [Item] & " AND [Whse] =
'" & [Whse] & "' AND [Week] = " & [Week])

Another way to do this is with an non-Equi join. You are used to creating
joins that look like:

SELECT A.SomeField, B.SomeField
FROM A INNER JOIN B ON A.JoinField = B.JoinField

Well, you can also create non-equal joins, like the following. The down
side of this is that you have to create this type of join in the SQL view.
I generally create the basic query and create a equi-join (in this case on
all three fields). Then I test it to make sure it is doing the equi-join
properly, then I go to the SQL view, modify the join and run the query
again.

SELECT A.Item, A.Whse, A.Week, A.Qty, Sum(B.Qty) as RunningSum
FROM yourTable AS A LEFT JOIN yourTable AS B
ON A.Item = B.Item
AND A.Whse = B.Whse
AND A.Week >= B.Week
GROUP BY A.Item, A.Whse, A.Week, A.Qty

You can test out both methods to determine which runs quicker in your
application, my guess is that it will be the latter.

Dale

Hello,

[quoted text clipped - 30 lines]

Best Regards,
James C.

 

[jversiz via AccessMonster.com]

Wow! This ended up being much more simple than I thought.

I created a query of tblQty, then created a copy of that query. In the copy
of the query, I put equal joins on item and whse, then put a less than or
equal to join on the week (use WHERE in the Total Week field of qryQty and
put criteria <=[tblQty].[Week]). Lastly, I added the Qty field from the
qryQty and set the total equal to sum.

I think this is the same thing you were telling me, Dale, but this method
uses Access to do the querying for me, as opposed to writing a sub-query in
SQL. Fortunately, I all my work is being done in Access, so I will not have
to worry as much about the speed of the query.

Case Closed!

Best Regards,
James C.

Hi Dale,

Both my Item and Whse fields are alpha-numeric. Would that eliminate option
one for me?

Regards,
James C.

James,

[quoted text clipped - 34 lines]