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
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.
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
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.
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:
CreatedDate (and other fields used) must be fields of the domain (here query InventoryTransactionsExtended). These cannot come flown from randomly occurring tables.
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.
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.
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.