DSum Running Total in query

[lucy6868]

I am trying to write a query that will give me a running total of the demand qty for each part number sorted by date. I have created a unique filed that includes part number, date, and order number that is unique for each record. I then sort on that unique filed.

Query1 UniqueRecord UniqueRecordAlias PartNo RT RT2 7306620140225300535230000 7306620140225300535230000 73066 3760.828
7306620140402300537900000 7306620140402300537900000 73066 3760.828
7306620140407300537910000 7306620140407300537910000 73066 3760.828
7306620140416300541090000 7306620140416300541090000 73066 3760.828

The field RT gives me a total of all demand for that part number. The field RT2 errors out.

SELECT qryMRPDetail_PartShortages_Sub.UniqueRecord, qryMRPDetail_PartShortages_Sub.UniqueRecordAlias, qryMRPDetail_PartShortages_Sub.PartNo, DSum("DemandQty","tblMRPDetail_PartShortages","[PartNo] = '73066'") AS RT, DSum("[DemandQty]","qryMRPDetail_PartShortages_Sub","[UniqueRecord] <= " & [UniqueRecordAlias] & "") AS RT2
FROM qryMRPDetail_PartShortages_Sub
GROUP BY qryMRPDetail_PartShortages_Sub.UniqueRecord, qryMRPDetail_PartShortages_Sub.UniqueRecordAlias, qryMRPDetail_PartShortages_Sub.PartNo
ORDER BY qryMRPDetail_PartShortages_Sub.UniqueRecord;

I tried doing the exercise in the Northwinds sample database and I could not get it to run either. All I got was #Error in my running total field.

Any help would be appreciated.

Lucy

 

[pbaldy]

What is the data type of the UniqueRecord field? You're treating it as a numeric field.

 

[lucy6868]

It is a text field. Sometimes there are alpha characters in the part number that I am using to create the UniqueRecord field.

 

[lucy6868]

Can you give me the syntax for treating it as a text field?

Thanks,

Lucy

 

[pbaldy]

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

 

[lucy6868]

This is giving me a total sum for all demand qty in the database, not judt the demand for the individual part number:

RT2: DSum("[DemandQty]","[qryMRPDetail_PartShortages_Sub]","[PartNo]= " & '[PartNo]' & " ")

What I would like is:

UniqueRecord UniqueRecordAlias PartNo DemandQty RT RT2 What I need
7306620140225300535230000 7306620140225300535230000 73066 12.74 3760.828 3760.828 12.74 7306620140402300537900000 7306620140402300537900000 73066 4.4 3760.828 3760.828 17.14 7306620140407300537910000 7306620140407300537910000 73066 33.016 3760.828 3760.828 50.16 7306620140416300541090000 7306620140416300541090000 73066 4.4 3760.828 3760.828 54.56 7306620140520300556760000 7306620140520300556760000 73066 76.44 3760.828 3760.828 131.00

 

[lucy6868]

I managed to get an entire total based on part number with this:

RT2: DSum("[DemandQty]","[qryMRPDetail_PartShortages_Sub]","[PartNo]= '" & [PartNo] & "'")

 

[pbaldy]

You would normally have 2 criteria, the second on a date or other field that you wanted the running total calculated on. In English, you want "where the part number is this part and the date is before or equal to this date".

 

[lucy6868]

Here's is my fresh attempt:

RT3: DSum("[DemandQty]","[qryMRPDetail_PartShortages_Sub]","[PartNo]= '" & [PartNo] & "'" And "[UniqueRecord] <= '" & [UniqueRecordAlias] & "'")

This returns the grand total of all demand for all part numbers. Different but not what I need.

 

[namliam]

your dsum should cause an error message....

RT3: DSum("[DemandQty]","[qryMRPDetail_PartShortages_Sub]","[PartNo]= '" & [PartNo] & "'" And "[UniqueRecord] <= '" & [UniqueRecordAlias] & "'")
The two quotes in red shouldnt be there....
Futher is UniqueRecord a number? if so you should remove the ' around the uniquerecordalias

 

[lucy6868]

UniqueRecord is a text field.

 

[pbaldy]

Take out the quotes namliam has in red.

 

[lucy6868]

The following formula worked correctly:

RT4: DSum("[DemandQty]","[tblMRPDetail_Demand_RunningTotal_DataFields]","[PartNo]= '" & [PartNo] & "' And [UniqueRecord] <= '" & [UniqueRecordAlias] & "'")

Thanks much for your help.

 

[pbaldy]

Happy to help.

 

[lucy6868]

Just when you thought you were rid of me...

Here is the next derivation. I am trying to get supply and demand by location out of a system that was built around only have one location. When I try to add location to the running total, I get the message: The expression you entered contains invalid syntax. The may have entered an operand without an operator.

RTLocation: DSum("[SupplyLessDemand]","[tblMRPDetail_Demand_RunningTotal_DataFields]", "[PartNo]= '" & [PartNo] & "' And "[WorkCenterLocation]= '" & [WorkCenterLocation] & "' And [UniqueRecord] <= '" & [UniqueRecordAlias] & "'")

 

[pbaldy]

Similar problem as before:

RTLocation: DSum("[SupplyLessDemand]","[tblMRPDetail_Demand_RunningTotal_DataFields]", "[PartNo]= '" & [PartNo] & "' And "[WorkCenterLocation]= '" & [WorkCenterLocation] & "' And [UniqueRecord] <= '" & [UniqueRecordAlias] & "'")

 

[namliam]

A double quote (") shall always be preceded, or followed by an &

You have (again) one " to many somewhere....