Solved DSum Criteria

[wajidpharmacist007]

I want to make running sum in Query for this i use dsum function. i have made two criteria parts separately and both criterias are working when applied individually. First one as

RT: DSum("[Actual Quantity]","[Inventory Transactions Extended]","[TransactionID]<=" & [TransactionID])

And Second One as

RT: DSum("[Actual Quantity]","[Inventory Transactions Extended]","[Inventory.ID]=" & [Inventory.ID])

but when i join two criteria in the following manner

RT: DSum("[Actual Quantity]","[Inventory Transactions Extended]","[TransactionID]<=" & [TransactionID] AND [Inventory].[ID]=" & [Inventory].[ID])

it returns error

Kindly Guide.
:banghead:

 

[pbaldy]

You didn't restart the fixed string. Try

DSum("[Actual Quantity]","[Inventory Transactions Extended]","[TransactionID]<=" & [TransactionID] & " AND [Inventory].[ID]=" & [Inventory].[ID])

 

[Minty]

Your a quote or two out - try;

RT: DSum("[Actual Quantity]","[Inventory Transactions Extended]","[TransactionID]<=" & [TransactionID] & " AND [Inventory].[ID]=" & [Inventory].[ID])

 

[Minty]

Baldy's obviously got his fast fingers out today

 

[pbaldy]

Playing with 2 year old grandson, have to have quick fingers to keep up with him.

 

[wajidpharmacist007]

You didn't restart the fixed string. Try

DSum("[Actual Quantity]","[Inventory Transactions Extended]","[TransactionID]<=" & [TransactionID] & " AND [Inventory].[ID]=" & [Inventory].[ID])

Thanks Brother it worked

 

[pbaldy]

No problem. Minty gave the same answer, just slower.

 

[Minty]

Don't keep on!

 

[wajidpharmacist007]

You didn't restart the fixed string. Try

DSum("[Actual Quantity]","[Inventory Transactions Extended]","[TransactionID]<=" & [TransactionID] & " AND [Inventory].[ID]=" & [Inventory].[ID])

Dear This is working well but now i want to run dsum with date criteria instead of TransactionID criteria. I am using this code

DSum("[Actual Quantity]","[Inventory Transactions Extended]","[Inventory Transactions].[Created Date]<=#" & [Inventory Transactions].[Created Date] & "# AND [Inventory].[ID]=" & [Inventory].[ID])

but the results are random, running sum value does not match the actual quantity in date order.
Please Guide

 

[Gasman]

What is this meant to do?

"[Inventory Transactions].[Created Date]<=#" & [Inventory Transactions].[Created Date] & "#

 

[Gasman]

What is the point of quoting your own post?

 

[ebs17]

It can't be like that, your presentation is somehow cobbled together.

RT: DSum("[Actual Quantity]","[Inventory Transactions Extended]","[Inventory.ID]=" & [Inventory.ID])

DSum("[Actual Quantity]","[Inventory Transactions Extended]","[Inventory Transactions].[Created Date]<=#" & [Inventory Transactions].[Created Date] & "# AND [Inventory].[ID]=" & [Inventory].[ID])

A running total would be created based on one table/query because that's the only way it makes sense.
But in the second expression you throw 3 (in words three!) tables mixed up. Your problem is completely different than criteria.

For a solution, if desired, you should present a complete task statement => sample database with tables involved and their relationships. Few but meaningful data in the tables. Plus the query in which the running total is to take place.

 

[wajidpharmacist007]

It can't be like that, your presentation is somehow cobbled together.

RT: DSum("[Actual Quantity]","[Inventory Transactions Extended]","[Inventory.ID]=" & [Inventory.ID])

DSum("[Actual Quantity]","[Inventory Transactions Extended]","[Inventory Transactions].[Created Date]<=#" & [Inventory Transactions].[Created Date] & "# AND [Inventory].[ID]=" & [Inventory].[ID])

A running total would be created based on one table/query because that's the only way it makes sense.
But in the second expression you throw 3 (in words three!) tables mixed up. Your problem is completely different than criteria.

For a solution, if desired, you should present a complete task statement => sample database with tables involved and their relationships. Few but meaningful data in the tables. Plus the query in which the running total is to take place.

Actually I have three tables
1- Inventory Table (Containing ID, Item and Category fields)
2- Inventory Transactions Table (Containing TransactionID, Created Date, Transaction Item, Transaction Type, Description and quantity fields).
3-Transaction Type Table (Containing ID and Transaction Type fields)

[Inventory Transactions].[Transaction Item] and [Inventory Transactions].[Transaction Type] are linked with [Inventory].[ID] and [Transaction Type].[ID] Tables with one to many relationship (enforcing referential integrity).

I have made another query "Inventory Transaction Extended" having all fields from all above three tables. SQL statement of this query is as;

SELECT [Inventory Transactions].ID AS TransactionID, [Inventory Transactions].*, [Transaction Types].*, Inventory.*, IIf([Transaction Types].[Add/Remove]="Addition",[Inventory Transactions]!Quantity,-([Inventory Transactions]!Quantity)) AS [Actual Quantity], DSum("[Actual Quantity]","[Inventory Transactions Extended]","[Inventory Transactions].[Created Date]<=#" & [Inventory Transactions].[Created Date] & "# AND [Inventory].[ID]=" & [Inventory].[ID]) AS [In Stock]
FROM Inventory INNER JOIN ([Inventory Transactions] LEFT JOIN [Transaction Types] ON [Inventory Transactions].[Transaction Type] = [Transaction Types].ID) ON Inventory.ID = [Inventory Transactions].[transaction Item];

I am using DSUM function in Inventory Transactions Extended query.
Previously I was using TransactionID and Inventory.ID as criteria in DSum. Now I want to use [Inventory Transactions].[Created Date] and [Inventory].[ID] as DSum criteria.
I am using this code

DSum("[Actual Quantity]","[Inventory Transactions Extended]","[Inventory Transactions].[Created Date]<=#" & [Inventory Transactions].[Created Date] & "# AND [Inventory].[ID]=" & [Inventory].[ID])

But the results that come are shown in this manner as given in attachment.

 

[ebs17]

For a solution, if desired, you should present a complete task statement => sample database with tables involved and their relationships. Few but meaningful data in the tables. Plus the query in which the running total is to take place.

Instead of quoting everything, you should read and do.

I am using DSUM function in Inventory Transactions Extended query.

On the one hand, you cannot first work in the query (quasi-create it) and, on the other hand, take it as already given in DSum. The dog should bite its own tail. This also applies to the calculated field ActualQuantity. If you want to get away without pain, you should think about it carefully.

Additionally, you are using DSum incorrectly. I shorten:

DSum("ActualQuantity", "InventoryTransactionsExtended", "CreatedDate <= #2023-04-28#")

CreatedDate (and other fields used) must be fields of the domain (here query InventoryTransactionsExtended). These cannot come flown from randomly occurring tables.

IIf([Transaction Types].[Add/Remove] = "Addition", [Inventory Transactions]!Quantity, -([Inventory Transactions]!Quantity)) AS [Actual Quantity]

The calculation with derivation of the sign is very cumbersome, should be derived directly from the same table => [Inventory Transactions].[Transaction Type].

Without the spaces and special characters, you can largely do without the stupid brackets, which only hinder the overview. Overview is good for reading, understanding, and improving your instructions.

 

[ebs17]

Apparently it doesn't go any further here.

Solved - DSum Criteria

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...

www.access-programmers.co.uk

Expecting feedback is certainly an outrageous demand.

 

[wajidpharmacist007]

Apparently it doesn't go any further here.

Solved - DSum Criteria

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...

www.access-programmers.co.uk

Expecting feedback is certainly an outrageous demand.

Thanks Alot ebs17 I finally solved by formating date parameter to SQL standard (yyyy-mm-dd) format and acheived desired result.
Thanks Alot.