DSum for Running Total (1 Viewer)

[FlashNZ]

Hi,

I have a table (Table is called "Item Data")where I am trying to calculate a running total per Item.

This is my expression: DSum([Quantity Purchased],"Item Data","[ItemID]=" & [ItemID])

Trouble is it's not calculating it correctly as you can see in the screen shot. ItemID 1775 should calculate to 5 at the last entry but instead it calculates to 60?

Please help.

 

[arnelgp]

DSum([Quantity Purchased],"Item Data","[ItemID]=" & [ItemID] & " And [FY] <= " & [FY] & " And [Period No] <= " & [Period No])

 

[FlashNZ]

DSum([Quantity Purchased],"Item Data","[ItemID]=" & [ItemID] & " And [FY] <= " & [FY] & " And [Period No] <= " & [Period No])

Thanks but it still calculates to 60?

 

[arnelgp]

copy and paste to new query (SQL view).

SELECT [Item Data].ItemID,
[Item Data].ItemNumber,
[Item Data].FY,
[Item Data].[Period No],
[Item Data].[Quantity Purchased],
(SELECT Top 1 Sum(T.[Quantity Purchased])
FROM [Item Data] AS T
Where T.ItemID = [Item Data].ItemID And
T.FY <= [Item Data].FY And
T.[Period No] <= [Item Data].[Period No]) AS Expr1
FROM [Item Data]
ORDER BY [Item Data].ID;

 

[FlashNZ]

Thanks, works perfectly.

 

[Gasman]

Isn't that query the same as the DSum?

 

[FlashNZ]

Not sure but it works. I added a Index column to my data and included that in the code so that it didn't recalculate after the end of a period. Thanks to arnelgp for the help.

 

[theDBguy]

Not sure but it works. I added a Index column to my data and included that in the code so that it didn't recalculate after the end of a period. Thanks to arnelgp for the help.

Hi. Welcome to AWF!

I'm just puzzled by your sample query. Do you know (or can you explain) where the 60 is coming from? Using DSum([Quantity Purchased],"Item Data","[ItemID]=" & [ItemID]), I was expecting to see a 5 in all the rows for the ItemID 1775. I must be missing something.

 

[FlashNZ]

Hi, I have no idea, that's why I posted in the forum. I tried different options but could never get the answer I wanted. I have never used DSum before and can't quite get my head around the proper syntax. My data has approx 45000 rows and previously I was calculating a running total in Excel using SUMIFS. It would take ages to calculate so that's why I was trying to get a solution in Access.

 

[theDBguy]

Hi, I have no idea, that's why I posted in the forum. I tried different options but could never get the answer I wanted. I have never used DSum before and can't quite get my head around the proper syntax. My data has approx 45000 rows and previously I was calculating a running total in Excel using SUMIFS. It would take ages to calculate so that's why I was trying to get a solution in Access.

Okay, thanks for the additional information. I guess we don't have to worry about it now, since the problem is already fixed. Good luck with your project.

 

[Pat Hartman]

Isn't that query the same as the DSum?

Using a corelated sub query is more efficient than using domain functions in a query. I think a simple join would be better since Access doesn't optimize sub queries very well.

Better still is to perform the process in the report rather than in a query. Use the control's Running Sum property to do the calculation. You can choose the Over All or Over Group option.