asked on

DSum function ignoiring criteria in Access

I am having some issues in access using the DSum function to create a running total in access. I am trying to create a running total for both my Production standard and total conforming parts by using the date as the criteria. I am also doing this in a totals query which is set to pull data between [start date] and [end date]. The formula is working correctly for my production standard creating a running total between the specified dates but the total conforming formula (which is the exact same formula) is summing all of the data and then creating a running total. The are the two formulas I am using:

Production Standard - Running Total: DSum("[Production Standard]","M151-3 by Month (with standards)","Date <= #" & [Date] & "#")

Total Conform- Running Total: DSum("[Total Conform]","Kylee M151-3 by Month (with standards)","Date <= #" & [Date] & "#")

I should also note that I used these exact formulas for a different query (sorting by a different shift) and they worked perfectly.

Production Standard - Running Total: DSum("[Production Standard]","M151-1A by Month (with standards)","Date <= #" & [Date] & "#")

Total Conform- Running Total: DSum("[Total Conform]","Kylee M151-1A by Month (with standards)","Date <= #" & [Date] & "#")

Can anyone tell me what I am possibly doing wrong and why theses equations work for some of my data and not the others? This is all pulled from the same source I am just creating different reports sorted by shift.

Dale Fye

Is this for a continuous form, or a single form, or a report?

If a report, you can simply add a textbox to the report, bind it to the field [Total Conform], the set the RunningSum property of the control to "Over Group" or whatever is appropriate for your situation.

If you are trying to do this as part of a query, I would strongly advise against it. using domain functions within queries will significantly slow down the query. Instead, I would recommend something like:

SELECT A.[DateField], A.[Total Conform], SUM(B.[Total Conform]) as RunningSum
FROM yourTable as A, yourTable as B
WHERE B.[DateField] <= A.[DateField]
GROUP BY A.[DateField]
ORDER BY A.[DateField]

Open in new window

This particular syntax would create a cartesian join and then filter so that it includes every record from B with a data <= the date in the A table. Another method, which is more efficient, but which cannot be written in the query designer (only the SQL view) would look like:

SELECT A.[DateField], A.[Total Conform], SUM(B.[Total Conform]) as RunningSum
FROM yourTable as A
LEFT JOIN yourTable as B ON B.[DateField] <= A.[DateField]
GROUP BY A.[DateField]
ORDER BY A.[DateField]

Open in new window

Kylee Davis

ASKER

I am using the query in order to create a graph which compares the running total of the production standard to the running total of the total conforming parts so this really has to be done in a query although I know it does slow down access drastically. I have included some screen shots comparing the two queries. The M151-1A is working correctly while the M151-3 is not but only for total conforming. I hope this is helpful.
M151-1A.png
M151-1A-Query.png
M151-1A-graph.png
M151-3.png
M151-3-Query.png

Gustav Brock

You need brackets and proper formatting of the date value:

Production Standard - Running Total: DSum("[Production Standard]","[M151-3 by Month (with standards)]","[Date] <= #" & Format([Date], "yyyy\/mm\/dd") & "#")

But you probably would be better off creating subqueries.

/gustav

Kylee Davis

ASKER

That formula seems to work better for what I am trying to do but it is still including data from previous dates not included in my query between [start date] and [end date]. For example I want the running total between 9/1/17 and 9/12/17 but it is including the data from the date 8/31/17 in my running total.

Gustav Brock

Then you must have a criteria with a min and max date. Right now, you have one date only to filter on.

/gustav

Kylee Davis

ASKER

That definitely makes sense as to why that criteria isn't working form me. I just assumed that it would also follow the criteria that I set up for the entire query. Would you possibly know how to write that into my criteria? It is completely stumping me every time I try to rewrite it I am getting a error message.

Gustav Brock

That would be something like:

Production Standard - Running Total: DSum("[Production Standard]","[M151-3 by Month (with standards)]","[Date] Between #" & Format([StartDate], "yyyy\/mm\/dd") & "# And #" & Format([EndDate], "yyyy\/mm\/dd") & "#")

/gustav

Kylee Davis

ASKER

Ok that formula is pulling the correct data but is now giving me the total sum instead of a running total. Would there be a way to write it as an IIF function so that if [Date] is between [Start Date] and [End Date] then DSum("[Production Standard]","[M151-3 by Month (with standards)]","[Date] <= #" & Format([Date], "yyyy\/mm\/dd") & "#")?

Dale Fye

So, using my previous example, I would rewrite it like:

SELECT A.[DateField], A.[Total Conform], SUM(B.[Total Conform]) as RunningSum
FROM yourTable as A
LEFT JOIN yourTable as B 
ON B.[DateField] >= [StartDate] 
AND B.[DateField] <= A.[DateField]
WHERE A.[DateField] BETWEEN [StartDate] and [EndDate]
GROUP BY A.[DateField]
ORDER BY A.[DateField]

Open in new window

Do you really have separate tables for each "part" (M151-A1, M151-3, ...)

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.