Solved DSum Criteria

[wajidpharmacist007]

I want to do running sum in Query for this i am using DSum function. I have Inventory Transactions table with Created Date, Transaction Item, quantity and description fields.
I have made query with all fields of this table and want to do running sum with Created Date wise and Transaction Item wise, for this I am running dsum in this way

=DSum("[Quantity]","[Inventory Transactions]","[Inventory Transactions].[Created Date]=#" & [Inventory Transactions].[Created Date] & "# AND [Inventory Transactions].[Transaction Item] = " & [Inventory Transactions].[Transaction Item])

But this does not work.
I do't know access pretty well, can work just graphically little bit and can not handle queries and functions more efficiently.
I just want to view total running transaction quantity of each item on daily basis for which I am doing so.
Is there any other way to do this please guide.
Thanks Alot.

 

[arnelgp]

Just create a Total (Aggregate query), you don't need Dsum.

Select InventoryID, [Created Date], Sum(Quantity) As SumOfQuantity From yourTableName Group By InventoryID, [Created Date];

 

[wajidpharmacist007]

Just create a Total (Aggregate query), you don't need Dsum.

Select InventoryID, [Created Date], Sum(Quantity) As SumOfQuantity From yourTableName Group By InventoryID, [Created Date];

I do't want to group my data. I need running total of each item quantity in separate field

As there is difference to execute DSum function criteria for text, number and date fields .

Mine Transaction item field type is number and created date field type is Date.

 

[Josef P.]

For a running sum a comparison with < or > must be used.

e. g.:

=DSum("[Quantity]","[Inventory Transactions]","[Created Date]<=#" & [Inventory Transactions].[Created Date] & "# AND [Transaction Item] = " & [Inventory Transactions].[Transaction Item])

[OT]
The above statement will throw an error if the implicit conversion of the date to text does not become a SQL conform date.

 

[wajidpharmacist007]

Thanks Josef P. I have followed your code as given

=DSum("[Quantity]","[Inventory Transactions]","[Created Date]<=#" & [Inventory Transactions].[Created Date] & "# AND [Transaction Item] = " & [Inventory Transactions].[Transaction Item])

But Results are given in attachment. DSum is not calculating running sum date wise. As on 09-July-2021 Running sum Value is 1366 and on 12-July-2021 total 23 quantity is added there should be 1389 but there is 2994. unable to understand from where this is doing.

 

[CJ_London]

As on 09-July-2021

That is a UK/Most of World format. For SQL statements (which is what your criteria is) you need to format your date into the SQL standard (yyyy-mm-dd) or US standard (mm-dd-yyyy). Personally I go for the SQL standard to avoid any risk of ambiguity

"[Created Date]<=#" & format([Inventory Transactions].[Created Date],"yyyy-mm-dd") & "# AND [Transaction Item] = " & [Inventory Transactions].[Transaction Item]

 

[Josef P.]

Additional to CJ_London: is there perhaps more than one [Transaction Item] (id) with the identical name?

 

[wajidpharmacist007]

That is a UK/Most of World format. For SQL statements (which is what your criteria is) you need to format your date into the SQL standard (yyyy-mm-dd) or US standard (mm-dd-yyyy). Personally I go for the SQL standard to avoid any risk of ambiguity

"[Created Date]<=#" & format([Inventory Transactions].[Created Date],"yyyy-mm-dd") & "# AND [Transaction Item] = " & [Inventory Transactions].[Transaction Item]

This Worked Thanks Alot CJ_London and Josef P.

 

[Gasman]

This Worked Thanks Alot CJ_London and Josef P.

Perhaps you can mark both posts as Solved then?

 

[wajidpharmacist007]

Perhaps you can mark both posts as Solved then?

Yes off course both solved
Thanks alot for guidance.

 

[Gasman]

So mark the thread solved please.