Microsoft Access - I'm having an issue using DSum() in a query and I cannot figure out the solution.

Asked By mark collins on 20-Jun-12 04:50 PM
I'm not a programmer by any means, I use the design view in Access to get what I need done so I'm getting lost trying to figure out how to write this DSum function...

The setup so far... linking two queries, Launch_NPI020 & Receiving020.  Taking all of the data from the Launch query and adding receipt quantity.  The Sold Qty from the Launch query is what I need to get as a running sum by date... The criteria I need to sum by are - Launch_NPI020.Carrier, Launch_NPI020.Model, Launch_NPI020.Color, Launch_NPI020.MonDate

Mondate is the date field... monday date of each week.


Here is the code so far... everything works but the DSum...
SELECT Launch_NPI020.Year, Launch_NPI020.Week, Launch_NPI020.WeekLookup, Launch_NPI020.MonDate, Launch_NPI020.Carrier, Launch_NPI020.Model, Launch_NPI020.Color, Launch_NPI020.SoldQty, IIf([Receiving020]![SumOfDelvyQty] Is Null,0,[Receiving020]![SumOfDelvyQty]) AS RecQty,

DSum("SoldQty","Launch_NPI020","Carrier = " & [Launch_NPI020]![Carrier]) AS TotalSold

FROM Launch_NPI020 LEFT JOIN Receiving020 ON (Launch_NPI020.Color = Receiving020.Color) AND (Launch_NPI020.Model = Receiving020.Model) AND (Launch_NPI020.Carrier = Receiving020.Carrier) AND (Launch_NPI020.WeekLookup = Receiving020.WeekLookup);

......

I keep getting a syntax error.  Do I need to force-define some of these field properties?  If so, how do I do this?  I saw somewhere to use a "Dim" function, but that just confused me.

Please help and thank you in advance

Desired output:

Year      Week   WeekLookup     MonDate            Carrier       Model       Color    SoldQty       RecQty      TotalSold
2012         18        201218          4/30/12               V              x75             B           10                7                 10
2012         18        201218          4/30/12               A              Y20             R           20                10               20 
2012         18        201218          4/30/12               V              x75             Z           30                8                 30 
2012         19        201219          5/07/12               V              x75             B           30                7                 40 
2012         19        201219          5/07/12               A              Y20             R           10                7                 30 
2012         19        201219          5/07/12               V              x75             Z           70                7                 100 


wally eye replied to mark collins on 21-Jun-12 10:50 AM
Could you use a GroupBy query?

SELECT Launch_NPI020.Year, Launch_NPI020.Week, Launch_NPI020.WeekLookup, Launch_NPI020.MonDate, Launch_NPI020.Carrier, Launch_NPI020.Model, Launch_NPI020.Color, Launch_NPI020.SoldQty, IIf([Receiving020]![SumOfDelvyQty] Is Null,0,[Receiving020]![SumOfDelvyQty]) AS RecQty, Sum(Launch_NPI020.SoldQty) AS TotalSold

FROM Launch_NPI020 LEFT JOIN Receiving020 ON (Launch_NPI020.Color = Receiving020.Color) AND (Launch_NPI020.Model = Receiving020.Model) AND (Launch_NPI020.Carrier = Receiving020.Carrier) AND (Launch_NPI020.WeekLookup = Receiving020.WeekLookup)

GROUP BY Launch_NPI020.Year, Launch_NPI020.Week, Launch_NPI020.WeekLookup, Launch_NPI020.MonDate, Launch_NPI020.Carrier, Launch_NPI020.Model, Launch_NPI020.Color, Launch_NPI020.SoldQty, IIf([Receiving020]![SumOfDelvyQty] Is Null,0,[Receiving020]![SumOfDelvyQty]) AS RecQty;

Then, on your report you can use the running sum to get your Total Sold column.
mark collins replied to wally eye on 21-Jun-12 11:24 AM
Unfortunately I do not think the group by will do anything to help.  It is already grouped in previous queries.  For each week, there is only one unique Carrier/Model/Color.  Grouping will give me the exact same information that I started with.
wally eye replied to mark collins on 21-Jun-12 09:08 PM
Short of using some VBA to build an intermediate table, your best option would be to sort on all identifying fields and create a report with running totals that breaks between groups to restart the running total.