CODEWITHSUNDEEP
sqlms-access
alarian
alarian

Reputation: 186

Query to get a running total of an "inventory change" column

I have a table in MS Access that looks like this MS Access printscreen clip

What I'm trying to achieve is to get a 6th field that shows the sum of all changes up until that date.

I've achieved this easily in Excel by adding a SUM IF calculation, to illustrate what I need help with. MS Exccel Printscreen clip

Now I need to remove Excel and have this done directly in Access.

Upvotes: 1

Views: 832

Answers (2)

Parfait
Parfait

Reputation: 107652

Consider a subquery for a running sum of Inventory:

Conditionally aggregate subquery

SELECT t1.[Date], t1.[Part SKU], t1.[SKU Name], t1.[SumOfInventory Change],
   (SELECT SUM(t2.[SumOfInventory Change]) 
     FROM [Inventory Report Table] t2
     WHERE t1.[Date] >= t2.[Date]
     AND t1.[Part SKU] = [t2.Part SKU]) AS [Inventory Level]
FROM [Inventory Report Table] t1

DSum() Subquery (using dynamic criteria for date condition)

SELECT t1.[Date], t1.[Part SKU], t1.[SKU Name], t1.[SumOfInventory Change],
   DSum("[SumOfInventory Change]", "[Inventory Report Table]", 
        "[Date] >=#" & t1.[Date] & "# AND [Part SKU] ='" & [t2.Part SKU] &"'") AS [Inventory Level]
FROM [Inventory Report Table] t1

If [Date] is stored as string and not date/time (from your post Access ACE/Jet does not by default store dates in that format which the other RDMS's do), replace # with single quotes.

Upvotes: 2

alarian
alarian

Reputation: 186

Thanks to the user Utsav who made me aware of Dsum, I managed to make this DSum function, which solves my problem.

Expr1: DSum("[SumOfInventory Change]";
  "Inventory Report Table";
  "[Part SKU] = '" & [Part SKU] &
  "' AND Date Between #" & Format("2015-01-01";"mm/dd/yyyy") &
  "# And #" & Format([Date];"mm/dd/yyyy") & "#"
)

Upvotes: 2

Related Questions