Query to sum sales totals for X number of months given dates

I would like to make a query that SUMS the total number of sales of each product ID for a group of months (ie. May – August)

I want:

+------------+-------------------------+-------+--------------+-----------------+
| PRODUCT_ID |          ITEM           | PRICE | GROSS_MARGIN | Sum Of QUANTITY |
+------------+-------------------------+-------+--------------+-----------------+
| 100-10     | ALVE laptop table       |    89 | 56%          |           23323 |
| 100-15     | FREDRIK desk            |   129 | 44%          |            9614 |
| 100-20     | GALANT conference table |   369 | 32%          |           10272 |
+------------+-------------------------+-------+--------------+-----------------+

But I am unable to set up the query to do this as I am very new to access (really my first time toying around with it).

This is what my current query spits out:

+------------+--------------------------+---------+--------------+--------------------+----------+
| PRODUCT_ID |           ITEM           |  PRICE  | GROSS_MARGIN | TRANSDATE By Month | QUANTITY |
+------------+--------------------------+---------+--------------+--------------------+----------+
| 100-10     | ALVE laptop table        | $89.00  | 56.00%       | August 2011        |     9679 |
| 100-10     | ALVE laptop table        | $89.00  | 56.00%       | July 2011          |     9436 |
| 100-10     | ALVE laptop table        | $89.00  | 56.00%       | June 2011          |     3222 |
| 100-10     | ALVE laptop table        | $89.00  | 56.00%       | May 2011           |      986 |
| 100-15     | FREDRIK desk             | $129.00 | 44.00%       | August 2011        |     3150 |
| 100-15     | FREDRIK desk             | $129.00 | 44.00%       | July 2011          |     2695 |
| 100-15     | FREDRIK desk             | $129.00 | 44.00%       | June 2011          |     3769 |
| 100-20     | GALANT conference table  | $369.00 | 32.00%       | August 2011        |     3814 |
| 100-20     | GALANT conference table  | $369.00 | 32.00%       | July 2011          |     4977 |
| 100-20     | GALANT conference table  | $369.00 | 32.00%       | June 2011          |      225 |
| 100-20     | GALANT conference table  | $369.00 | 32.00%       | May 2011           |     1256 |
+------------+--------------------------+---------+--------------+--------------------+----------+

Essentially, I want one row to sum up all sales for that item for those 4 months (May to August, inclusive of every day in the month). Ignore other columns (price & margin) if necessary.

Any help would be greatly appreciated, I need to have this done by Friday! Have been playing with it for hours but I seem to break something every time I get one step closer. :'(

Here is my current SQL syntax/code (sorry, unaware of the proper term).

SELECT DISTINCTROW 
     PRODUCTS.PRODUCT_ID, 
     PRODUCTS.ITEM, 
     PRODUCTS.PRICE, 
     PRODUCTS.GROSS_MARGIN,
     Format$([SALES].[TRANSDATE],'mmmm yyyy') AS [TRANSDATE By Month], 
     Sum(SALES.QUANTITY) AS [Sum Of QUANTITY]
FROM PRODUCTS INNER JOIN 
     SALES ON PRODUCTS.[PRODUCT_ID] = SALES.[PRODUCT_ID]
GROUP BY 
     PRODUCTS.PRODUCT_ID, 
     PRODUCTS.ITEM, 
     PRODUCTS.PRICE, 
     PRODUCTS.GROSS_MARGIN, 
     Format$([SALES].[TRANSDATE],'mmmm yyyy'), Year([SALES].[TRANSDATE])*12+DatePart('m',[SALES].[TRANSDATE])-1;

This actually currently shows all data for all months and years (I have 2 years worth of data for all products). I just want total sales for each product ID for the four months in one single year (May to August of 2011). PLEASE HELP!

Answer

As this is Access, SQL Fiddle can only be a start.

You need to “quote” the date expressions correctly:

SELECT 
     PRODUCTS.PRODUCT_ID, 
     PRODUCTS.ITEM,      
     Sum(SALES.QUANTITY) AS [Sum Of QUANTITY]
FROM 
     PRODUCTS INNER JOIN 
     SALES ON PRODUCTS.[PRODUCT_ID] = SALES.[PRODUCT_ID]
WHERE 
    [SALES].[TRANSDATE] BETWEEN #2015-05-01# AND #2015-08-31#
GROUP BY 
     PRODUCTS.PRODUCT_ID, 
     PRODUCTS.ITEM

Correction 2015-07-23

I’m terribly sorry for missing that your original data had dates for 2011 and not 2011. I hereby post the corrected SQL for you to test:

SELECT 
     PRODUCTS.PRODUCT_ID, 
     PRODUCTS.ITEM,      
     Sum(SALES.QUANTITY) AS [Sum Of QUANTITY]
FROM 
     PRODUCTS INNER JOIN 
     SALES ON PRODUCTS.[PRODUCT_ID] = SALES.[PRODUCT_ID]
WHERE 
    [SALES].[TRANSDATE] BETWEEN #2011-05-01# AND #2011-08-31#
GROUP BY 
     PRODUCTS.PRODUCT_ID, 
     PRODUCTS.ITEM

This will produce an output from your Access database. For use with SQL Fiddler it will, however, be different:

SELECT 
     PRODUCTS.PRODUCT_ID, 
     PRODUCTS.ITEM,      
     Sum(SALES.QUANTITY) AS [Sum Of QUANTITY]
FROM 
     PRODUCTS INNER JOIN 
     SALES ON PRODUCTS.[PRODUCT_ID] = SALES.[PRODUCT_ID]
WHERE 
    [SALES].[TRANSDATE] BETWEEN '2011-05-01' AND '2011-08-31'
GROUP BY 
     PRODUCTS.PRODUCT_ID, 
     PRODUCTS.ITEM

Sorry for the inconvenience.