DSum for Access
Hi.
I have a table named 2_WIP. In this table, I am interested in having a running total and also subtracting the running total off. May seem confusing but you can refer to the picture I embedded below. I am seeking for assistance whether I can put Dsum in a calculated field in Table.
I have researched everywhere regarding DSum for table but no avail for solutions. Thus, this explains why I did it in a Query.
The picture shows a Query that I have been working on to calculate my DSum. I have somewhat successfully created a DSum calculation in my Query. However, it is not exactly accurate.
The code:
Okay, here is the thing. If you focus on the Product "Test", for RecordID 3 the Total_WIP is correct.
However, since I have 600 WIP_Cleared for RecordID 3, it should affect the rest of the records because I have subtracted off 600 from the original running total.
Thus, instead of RecordID 4 Total_WIP is 1648 = 684 + 964, it should also minus RecordID 3's WIP_Cleared. How can I do this? This continues on for the same Product.
I have a table named 2_WIP. In this table, I am interested in having a running total and also subtracting the running total off. May seem confusing but you can refer to the picture I embedded below. I am seeking for assistance whether I can put Dsum in a calculated field in Table.
I have researched everywhere regarding DSum for table but no avail for solutions. Thus, this explains why I did it in a Query.
The picture shows a Query that I have been working on to calculate my DSum. I have somewhat successfully created a DSum calculation in my Query. However, it is not exactly accurate.
The code:
Total_WIP: DSum("[Total_Good_Count]","2_WIP","[Product]='" & [Product] & "' and [RecordID] <= " & [RecordID] & "") - [WIP_Cleared]Okay, here is the thing. If you focus on the Product "Test", for RecordID 3 the Total_WIP is correct.
However, since I have 600 WIP_Cleared for RecordID 3, it should affect the rest of the records because I have subtracted off 600 from the original running total.
Thus, instead of RecordID 4 Total_WIP is 1648 = 684 + 964, it should also minus RecordID 3's WIP_Cleared. How can I do this? This continues on for the same Product.
Well first, you really don't want to use a DSUM (or any of the domain functions for that matter) in q query. Domain functions represent SQL statements, which can be written directly. By using them in a query, you guarentee poor performance because the query optimizer can't do anything with them. The domain functions exist so you can use SQL in places where SQL is not allowed.
Second, as hnasr said, not the usual place to do this. If your doing it for a report, you should be aware that reporting has this functionality built-in, and it's much more efficient.
But if you really want to do this, you can. You just need a key that is incremental, such as a date/time. What your query needs to have is a sub-select that adds every record prior to the row your currently on. So even with the sub-select, this is going to be slow.
The format of this is:
SELECT fieldlist,
(SELECT Sum(FieldIWantToTotal) AS Total
FROM myTable
WHERE myTableKeyField <= T1.myTableKeyField) AS Total
FROM myTable AS T1)
That whole part in the () you'd put into a column.
Jim.
Second, as hnasr said, not the usual place to do this. If your doing it for a report, you should be aware that reporting has this functionality built-in, and it's much more efficient.
But if you really want to do this, you can. You just need a key that is incremental, such as a date/time. What your query needs to have is a sub-select that adds every record prior to the row your currently on. So even with the sub-select, this is going to be slow.
The format of this is:
SELECT fieldlist,
(SELECT Sum(FieldIWantToTotal) AS Total
FROM myTable
WHERE myTableKeyField <= T1.myTableKeyField) AS Total
FROM myTable AS T1)
That whole part in the () you'd put into a column.
Jim.
ASKER
Picture below is the Table 2_WIP. So where do I put all the codes mentioned above? I am a bit confuse. Sorry I am not proficient in Access.
It would go in your query. You can enter the statement directly by switching to sql view, or use the graphical editor.
If using the editor, right click on the table and then click properties. Under Alias, enter T1.
Then in a column in the grid, do:
Total: (SELECT Sum([Total_Good_Count]) FROM 2_WIP WHERE Date_Recorded<= T1.Date_Recorded)
Jim.
If using the editor, right click on the table and then click properties. Under Alias, enter T1.
Then in a column in the grid, do:
Total: (SELECT Sum([Total_Good_Count]) FROM 2_WIP WHERE Date_Recorded<= T1.Date_Recorded)
Jim.
ASKER
I'm not using the editor. So how do I put the codes in my query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
All calculations can be done in forms, reports, or even in queries.
The results you get will always be up-to-date.