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 · Accepted Answer · 2016-10-12 20:41:14Z

0

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

answered Oct 12, 2016 at 20:41

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sarah Over a year ago

Thanks, it was a typo, but it was not my problem. But I did make a mistake on thinking of MTD and QTD are the same with YTD. When doing MTD, I will have to aggregate from each new month. I will edit this post. Thanks.

Collectives™ on Stack Overflow

MS Access MTD/QTD QUERY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related