Having issues with DSUM not calculating as expected

[mittra]

I am preparing a report that takes an export of work items and determines which items can be done in a given year based on a given amount of funding. Essentially, my export is a list of work items that need to be done and I need to see which items can be completed if I had, for example, $100K that year. I know that the report function of Access can give me a running sum, but I need this in a query form because it will actually be a subquery.

The table is called "tblWorkingData" and the fields are WorkItemID (this is an autonumber for each record in the table), LICost (this is how much the work item will cost), and Score (this prioritizes the work items based on another algorithm in the database where the work items are created). I need a running total of LICost. Currently, DSUM is calculating the LICost multiplied by the record number.

Here's the query (in SQL form):

SELECT tblWorkingData.WorkItemID, Sum(tblWorkingData.[ACTUAL COST]) AS LICost, tblWorkingData.SCORE, DSum([LICost],"tblWorkingData","[WorkItemID] <=" & [WorkItemID]) AS RunTotal
FROM tblWorkingData
GROUP BY tblWorkingData.WorkItemID, tblWorkingData.SCORE
ORDER BY tblWorkingData.SCORE DESC;

And here's the current output:

WorkItemID	LICost	SCORE	RunTotal
30	330	77.2207792207792	330
31	475	77.2207792207792	950
32	610	76.1818181818182	1830
33	2250	75.1428571428571	9000
34	7300	74.1038961038961	36500
35	3800	72.1818181818182	22800
36	4000	72.1818181818182	28000
37	4000	72.1818181818182	32000
38	3800	72.1818181818182	34200
39	4000	72.1818181818182	40000
40	4000	72.1818181818182	44000
41	9900	71.1428571428572	118800
42	9300	71.1428571428571	120900
43	222000	70.987012987013	3108000
44	28500	70.1038961038961	427500
45	2300	69.2207792207792	36800
46	614000	61.0649350649351	10438000
47	65	55.9350649350649	1170
48	950	52.5714285714286	18050
49	950	52.5714285714286	19000
50	65	47.7922077922078	1365
51	65	47.7922077922078	1430
52	65	47.7922077922078	1495
53	65	47.7922077922078	1560
54	65	47.7922077922078	1625
55	65	40.3896103896104	1690
56	65	40.3896103896104	1755
57	65	40.3896103896104	1820
58	65	40.3896103896104	1885

So although WorkItemID starts at 30, it is Record #1 and the Dsum has multiplied the LICost by the record number. You can see this as early as WorkItemID 31. The LICost is 475 and dsum has returned 950 (475 x 2).

How do I change the criteria portion of the dsum statement to start performing the running total correctly?

[pbaldy]

I would try summing the field in the table, which appears to be [ACTUAL COST], not LICost. The field name needs quotes around it too.

[ranman256]

Access is iiffy on doing line#s and running totals. you have to know exactly what its counting.
So I usually , write the records to a table, then run code to sum it in the table, so there no doubt of whats getting counted.

'usage:  RunningTotal "qsData", "salary", "summ"

Private Sub RunningTotal(ByVal pvQry, ByVal pvSrcFld, ByVal pvSumFld)
Dim db As Database
Dim rst
Dim vSrc, vSumm
On Error GoTo ErrRun
Set db = CurrentDb
Set rst = db.OpenRecordset(pvQry)
With rst
  While Not .EOF
     vSrc = .Fields(pvSrcFld).Value
     vSumm = vSumm + vSrc
     
     .Edit
     .Fields(pvSumFld).Value = vSumm
     .Update
     
     .MoveNext
  Wend
End With
DoCmd.OpenQuery pvQry
endit:
Set rst = Nothing
Set db = Nothing
Exit Sub
ErrRun:
MsgBox Err.Description, , Err
GoTo endit
End Sub

[mittra]

Okay. So I changed my SQL to look like this:

SELECT tblWorkingData.WorkItemID, Sum(tblWorkingData.[ACTUAL COST]) AS LICost, tblWorkingData.SCORE AS LIScore, DSum("[Actual Cost]","tblWorkingData","[WorkItemID] <= " & [WorkItemID]) AS LITotal
FROM tblWorkingData
GROUP BY tblWorkingData.WorkItemID, tblWorkingData.SCORE
ORDER BY tblWorkingData.SCORE DESC;

And that appears to have cleared things up; however, I am concerned because this is supposed to run as a subquery and is supposed to stop when I hit a given funding amount (e.g., $100K).

Why does it seem to work on the column name [Actual Cost], but not on the alias for the sum([Actual Cost])?

[pbaldy]

The alias doesn't exist in the table you've directed it to.

[mittra]

Ah. That makes sense.

Is there a way in Access to do the equivalent of an autonumber field within a query? I actually have an additional field that is a true/false field that if it is true the record needs to be brought to the top of the list, but if I make WorkItemIDs 48 & 49 (for example) true, it does bring them to the top of the list, but the dsum does its calculations as if they were still in place because I understand that it is doing the sum based on the order of the WorkItemID.

The SQL of the query would look like this with the "Must Do" field added:

SELECT tblWorkingData.WorkItemID, Sum(tblWorkingData.[ACTUAL COST]) AS LICost, tblWorkingData.[MUST DO] AS LIMustDo, tblWorkingData.SCORE AS LIScore, DSum("[Actual Cost]","tblWorkingData","[WorkItemID] <= " & [WorkItemID]) AS LITotal
FROM tblWorkingData
GROUP BY tblWorkingData.WorkItemID, tblWorkingData.[MUST DO], tblWorkingData.SCORE
ORDER BY tblWorkingData.[MUST DO] DESC , tblWorkingData.SCORE DESC;

And the results then look like this:

WorkItemID	LICost	LIMustDo	LIScore	LITotal
48	950	True	52.5714285714286	921580
49	950	True	52.5714285714286	922530
30	330	False	77.2207792207792	330
31	475	False	77.2207792207792	805
32	610	False	76.1818181818182	1415
33	2250	False	75.1428571428571	3665
34	7300	False	74.1038961038961	10965
35	3800	False	72.1818181818182	14765
36	4000	False	72.1818181818182	18765
37	4000	False	72.1818181818182	22765
38	3800	False	72.1818181818182	26565
39	4000	False	72.1818181818182	30565
40	4000	False	72.1818181818182	34565
41	9900	False	71.1428571428572	44465
42	9300	False	71.1428571428571	53765
43	222000	False	70.987012987013	275765
44	28500	False	70.1038961038961	304265
45	2300	False	69.2207792207792	306565
46	614000	False	61.0649350649351	920565
47	65	False	55.9350649350649	920630
50	65	False	47.7922077922078	922595
51	65	False	47.7922077922078	922660
52	65	False	47.7922077922078	922725
53	65	False	47.7922077922078	922790
54	65	False	47.7922077922078	922855
55	65	False	40.3896103896104	922920
56	65	False	40.3896103896104	922985
57	65	False	40.3896103896104	923050
58	65	False	40.3896103896104	923115

So I would need to add an additional field that dsum could use that is an autonumber field so that it would see WorkItemID 48 as record 1.

In a macro, I would run the query without the dsum as a Make Table query, run a query against the new table with the dsum and then delete the table. I'm told in SQL I could make a temp table as part of a query, but it doesn't appear Access allows for the creation of temp tables within a query?

[pbaldy]

This might work, based on how Access stores True/False:

DSum("[Actual Cost]","tblWorkingData","[WorkItemID] <= " & [WorkItemID] & " AND [MUST DO] <= " & [MUST DO])

SQL Server (T-SQL) allows temp table creation within a query (more accurately a stored procedure); to my knowledge Access does not.

[mittra]

Thanks for the attempt, but the dsum field returned "#Error" for the two [MUST DO] = True" fields and null for the rest.

Is there something I could do with DCount to generate an autoincrementing ID number?

[pbaldy]

The problem is the number would need to be in the table the DSum() is based on. Can you post a sample of the db here? I thought that criteria would work.

[mittra]

SampleWorkingData.zip

This is the table and the query I'm working on now from the larger database. I have removed any non-related tables and queries.

[pbaldy]

The Must Do field is text, not Yes/No (True/False). It works if converted to Yes/No.

[mittra]

Kind of. It starts the sum over at the first Must Do false.

SQL:
SELECT tblWorkingData.WorkItemID, Sum(tblWorkingData.[ACTUAL COST]) AS LICost, tblWorkingData.[MUST DO] AS LIMustDo, tblWorkingData.SCORE AS LIScore, DSum("[Actual Cost]","tblWorkingData","[WorkItemID] <= " & [WorkItemID] & " AND [MUST DO] <= " & [MUST DO]) AS LITotal
FROM tblWorkingData
GROUP BY tblWorkingData.WorkItemID, tblWorkingData.[MUST DO], tblWorkingData.SCORE
ORDER BY tblWorkingData.[MUST DO], tblWorkingData.SCORE DESC;

Results:

WorkItemID	LICost	LIMustDo	LIScore	LITotal
48	950	Yes	52.5714285714286	950
49	950	Yes	52.5714285714286	1900
30	330	No	77.2207792207792	330
31	475	No	77.2207792207792	805
32	610	No	76.1818181818182	1415
33	2250	No	75.1428571428571	3665
34	7300	No	74.1038961038961	10965
35	3800	No	72.1818181818182	14765
36	4000	No	72.1818181818182	18765
37	4000	No	72.1818181818182	22765
38	3800	No	72.1818181818182	26565
39	4000	No	72.1818181818182	30565
40	4000	No	72.1818181818182	34565
41	9900	No	71.1428571428572	44465
42	9300	No	71.1428571428571	53765
43	222000	No	70.987012987013	275765
44	28500	No	70.1038961038961	304265
45	2300	No	69.2207792207792	306565
46	614000	No	61.0649350649351	920565
47	65	No	55.9350649350649	920630
50	65	No	47.7922077922078	922595
51	65	No	47.7922077922078	922660
52	65	No	47.7922077922078	922725
53	65	No	47.7922077922078	922790
54	65	No	47.7922077922078	922855
55	65	No	40.3896103896104	922920
56	65	No	40.3896103896104	922985
57	65	No	40.3896103896104	923050
58	65	No	40.3896103896104	923115

[pbaldy]

Sorry, I thought that's what you wanted. You aren't working with SQL Server I take it? It has a row_number function that could be used to number the result.

[mittra]

Unfortunately, this is 100% Access (need to eventually make it an executable to pass off to the client). If I could use SQL I wouldn't have needed to mess with DSUM at all, but could have used OVER as part of a subquery.

I can always go the route of making a table, adding the autonumber field, running my dsum query against that table and then deleting the table. Huge PITA, but if it's the only way to get my results, then it's what I'll do...

[mittra]

Okay.... I changed the way the append query worked to take my raw data and appended to my "Working Data" table (tblWorkingData) so that everything is in the proper order (Must Do's at the top and Ordered by Score descending). I then took the above SQL statement (that I was using simply to understand DSUM) and incorporated it into my larger query and now I'm getting an error again.

New SQL statement:

SELECT SubQ2.LineItem, SubQ2.LICost, SubQ2.LIMustDo, SubQ2.LIScore, DSum("[LICost]","SubQ2","[LineItem] <= " & [LineItem]) AS LITotal
FROM (SELECT SubQ1.LineItem, Sum(SubQ1.[Actual Cost]) AS LICost, Max(SubQ1.[must do]) AS LIMustDo, Max(SubQ1.score) AS LIScore
FROM (SELECT IIf(IsNull([tblWorkingData]![PROJECT]),[tblWorkingData]![WorkItemID],[tblWorkingData]![PROJECT]) AS LineItem, tblWorkingData.[ACTUAL COST], tblWorkingData.SCORE, tblWorkingData.[MUST DO]
FROM tblWorkingData) AS SubQ1
GROUP BY SubQ1.LineItem
ORDER BY Max(SubQ1.[must do]), Max(SubQ1.score) DESC) AS SubQ2
GROUP BY SubQ2.LineItem, SubQ2.LICost, SubQ2.LIMustDo, SubQ2.LIScore
ORDER BY SubQ2.LIMustDo, SubQ2.LIScore DESC;

LineItem	LICost	LIMustDo	LIScore	LITotal
59	950	-1	52.5714285714286	#Error
60	950	-1	52.5714285714286	#Error
61	475	0	77.2207792207792	#Error
62	330	0	77.2207792207792	#Error
63	610	0	76.1818181818182	#Error
64	2250	0	75.1428571428571	#Error
65	7300	0	74.1038961038961	#Error
Plumbing Replacement	52100	0	72.1818181818182	#Error
72	9900	0	71.1428571428572	#Error
73	9300	0	71.1428571428571	#Error
74	222000	0	70.987012987013	#Error
76	2300	0	69.2207792207792	#Error
77	614000	0	61.0649350649351	#Error
78	65	0	55.9350649350649	#Error
79	65	0	47.7922077922078	#Error
80	65	0	47.7922077922078	#Error
81	65	0	47.7922077922078	#Error
82	65	0	47.7922077922078	#Error
83	65	0	47.7922077922078	#Error
84	65	0	40.3896103896104	#Error
85	65	0	40.3896103896104	#Error
86	65	0	40.3896103896104	#Error
87	65	0	40.3896103896104	#Error