dSum for running total

[ConfusedMan25]

I have researched and researched in this forum for dSum function to create running total for my data. Although there are numerous threads in regards to this subject, I have yet to come up with the exact syntax to compute what I need based on a category and date. Please see below sample data. Note: some dates may repeat for any given PN. Please help with the syntax to compute CumQty. Would dSum work based on data from a query, as opposed to a table?

Is dSum the best option for me, or there is another option for my needs. I do intend to use this result set to perform another query, if that is a consideration at all.

 

[pbaldy]

It would help if you posted your effort. Given your sample, you'd need another field so records with the same date don't get the same running total.

 

[wazz]

there is a way, but as suggested, you'll need a unique identifier - an ID of some sort. see pic.
(in my pic, field3 is the same as your Qty field).

 

[pbaldy]

Of course the date would have to be added to the criteria as well.

 

[maggioant]

Hit the sigma icon for totals. Create a new column and select "Expression" for total type.

Your new column should be:

CumQt: DSum("[Qty]","Your_Query_Name","[DemandDate]<=" & [DemandDate] & "")

 

[pbaldy]

CumQt: DSum("[Qty]","Your_Query_Name","[DemandDate]<=" & [DemandDate] & "")

That is not the correct syntax for a date value, which presumably this field is:

http://www.mvps.org/access/general/gen0018.htm

It also doesn't account for the duplicate date values. They will end up with the same value.

 

[wazz]

right, i didn't use all the fields there.

 

[wazz]

here's another link:
http://support.microsoft.com/kb/290136

 

[ConfusedMan25]

I've tried so many different suggestions, but I am getting either blank results or #error. When do I use alias and how should the syntax be for the dates?

 

[pbaldy]

Check the link I posted in post 6. You need to concatenate the values.

 

[ConfusedMan25]

After much research from other posts, I’ve finally gotten the results I needed. I am actually using data from another query, instead of a table. In the attached, you shall see the syntax I used. The problem I have is that my result can range from few hundred to over a thousand. The data is shown line by line, which takes forever to populate.

I am still searching for a better way to do it than using dSum. Below is the SQL.

SELECT qryProductionPlanning_1.PartNumber, qryProductionPlanning_1.DueDate, qryProductionPlanning_1.BO_Qty, Val(DSum(" BO_Qty","qryProductionPlanning_1","PartNumber ='" & PartNumber & "' and DueDate <= #" & DueDate & "#")) AS RunningSum
FROM qryProductionPlanning_1
ORDER BY qryProductionPlanning_1.PartNumber, qryProductionPlanning_1.DueDate;

 

[wazz]

might not make a difference but you could try creating another query to use as the basis for this one, but remove the fields you aren't using in your calculation.