Solved DSum Criteria (1 Viewer)

Local time
Today, 11:45
Joined
Nov 23, 2018
Messages
31
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

Wino Moderator
Staff member
Local time
Today, 00:45
Joined
Aug 30, 2003
Messages
36,118
You didn't restart the fixed string. Try

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

Minty

AWF VIP
Local time
Today, 07:45
Joined
Jul 26, 2013
Messages
10,355
Your a quote or two out - try;

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

Minty

AWF VIP
Local time
Today, 07:45
Joined
Jul 26, 2013
Messages
10,355
Baldy's obviously got his fast fingers out today :)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:45
Joined
Aug 30, 2003
Messages
36,118
Playing with 2 year old grandson, have to have quick fingers to keep up with him. :p
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:45
Joined
Aug 30, 2003
Messages
36,118
No problem. Minty gave the same answer, just slower. :D
 
Local time
Today, 11:45
Joined
Nov 23, 2018
Messages
31
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
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

Enthusiastic Amateur
Local time
Today, 07:45
Joined
Sep 21, 2011
Messages
14,050
What is this meant to do? :unsure:

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

Gasman

Enthusiastic Amateur
Local time
Today, 07:45
Joined
Sep 21, 2011
Messages
14,050
What is the point of quoting your own post? :(
 

ebs17

Well-known member
Local time
Today, 08:45
Joined
Feb 7, 2020
Messages
1,883
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.
 
Local time
Today, 11:45
Joined
Nov 23, 2018
Messages
31
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;

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

Attachments

  • DSum Results.PNG
    DSum Results.PNG
    13.4 KB · Views: 45

ebs17

Well-known member
Local time
Today, 08:45
Joined
Feb 7, 2020
Messages
1,883
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:
Code:
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.

Code:
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

Well-known member
Local time
Today, 08:45
Joined
Feb 7, 2020
Messages
1,883
Apparently it doesn't go any further here.
Expecting feedback is certainly an outrageous demand.
 
Local time
Today, 11:45
Joined
Nov 23, 2018
Messages
31
Apparently it doesn't go any further here.
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.
 

Users who are viewing this thread

Top Bottom