MS Access MTD/QTD QUERY

Question

I get weekly data and have to make report every week with MTD, QTD AND YTD on the same table. I have columns Start Date (Monday) and End Date (Sunday), month number, week number, year, quarter number and Dollars Sold and Units Sold of each product. I have the following query to get MTD inspired by other post:

SELECT b.month AS Month, 
       b.[Vendor Part] AS VendorID, 
      (SELECT sum(a.[Dollars_Sold]) FROM [DATA] AS a 
       WHERE a.Month<=b.Month 
             AND a.Year=b.Year 
             AND a.[Vendor Part]=b.[Vendor Part]) AS MTD_Dollars
FROM DATA AS b
GROUP BY  [Vendor Part], Month, Year

When I try to run the query, the following dialog box pops out:

Please check the picture for dialog box

Vendor part is the unique ID of each product. Then after a long time waiting, the query will return a result only with totals for each month.

Please check the results

Does anyone know what is wrong? I don't want the dialog box out, I want the query to be able to produce a table with each product's MTD/QTD/YTD.

This query works for totals of each month, but not for each product in each month. Please help. Thank you.

Gustav Gustav · Accepted Answer · 2016-10-12T20:41:14

You miss a comma:

SELECT b.month AS Month, 
       b.[Vendor Part] AS VendorID, 

      (SELECT sum(a.[Dollars_Sold]) FROM [DATA] AS a 
       WHERE a.Month<=b.Month 
             AND a.Year=b.Year 
             AND a.[Vendor Part]=b.[Vendor Part]) AS MTD_Dollars,

      (SELECT sum(a.[Dollars_Sold]) FROM DATA AS a 
       WHERE a.Month<=b.Month 
             AND a.Year=b.Year 
             AND a.[Vendor Part]=b.[Vendor Part]) AS MTD_Units

FROM DATA AS b
GROUP BY  b.[Vendor Part], a.Month,a.Year

MS Access MTD/QTD QUERY

1 Answers