How do I Create Aggregate Sales Grouped by Product ID?

[kevinhadrice]

Hi guys, I'm a noob at Access and I have created a Query that shows



the Product ID, Product Name, Quantity, Price, Gross Margin, Sales, Profit.

The only problem is that in the two tables I was given in my assignment, there were multiple dates for the purchase, thus even though I have 74 products there is multiple listings

ie.

110-10 Sofa Chair 5x $2 profit:$10 - october 11
110-10 Sofa Chair 6x $2 profit:$12 - october 12

I need to group it so that it becomes this.

110-10 Sofa Chair 11x $2 profit:$22

The date/location is not important, only the aggregate sales. Here is my SQL but hopefully you can find me a solution in design view, I'm very bad with code.

SELECT SALES.PRODUCT_ID, PRODUCTS.ITEM, PRODUCTS.PRICE, SALES.QUANTITY, PRODUCTS.GROSS_MARGIN, Sum([Quantity]*[Price]) AS Sales, [Quantity]*[Price]*[Gross_Margin] AS Profit, PRODUCTS.WIDTH, PRODUCTS.DEPTH
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
GROUP BY SALES.PRODUCT_ID, PRODUCTS.ITEM, PRODUCTS.PRICE, SALES.QUANTITY, PRODUCTS.GROSS_MARGIN, [Quantity]*[Price]*[Gross_Margin], PRODUCTS.WIDTH, PRODUCTS.DEPTH, SALES.TRANSDATE
HAVING (((SALES.TRANSDATE)>=#9/1/2011# And (SALES.TRANSDATE)<=#12/31/2011#))
ORDER BY Sum([Quantity]*[Price]) DESC;

Nordic_Inventory1.zip

[pbaldy]

In design view, change Group By to Where on the TRANSDATE field.

[kevinhadrice]

Hi, I have done that but it still shows 959 results and the same items aren't grouped together.

[pbaldy]

Probably because you're grouping on things like quantity and price, which can vary. When you include a field with Group By, it will group by each different value in that field.

[June7]

Do an aggregate query on the Sales table alone. Then join that query to Products table if you want to show all products even if they don't have Sales data (use LEFT or RIGHT join, I forget which, not INNER) and/or you need additional info from Products table.

[kevinhadrice]

Yeah, I think so? My Sales is

Sales: Sum([Quantity]*[Price])
Grouped by: Expression

So I don't understand what do I need to do to group all my items and their sales& profit together to create aggregate sales of each product?

[pbaldy]

I'll get out of June7's way.

[kevinhadrice]

Hi June7,

In order to do so I would have more than one Query and it would have modified the two original tables provided.
Is there any way to do this within my criteria?

Criteria:
Your database file should contain a single saved query named “QuerySalesFall2014” plus the original two tables
provided. The query should join the necessary tables and apply criteria to limit the results to those required for
subsequent contribution analysis.

The Products table contains information about items available for sale. The Product_ID code reflects the Category of
the inventory item (100 = desks & tables, 200 = chairs, 300 = storage & shelves, 400 = boxes, 500 = lamps, 600 = vases,
700 = decoration, 800 = rugs, 900 = beds). The table also includes product’s physical dimensions (in centimeters), selling
price, and gross margin (expressed in percent).

The Sales table provides the sales quantity (in number of units sold) by date and retail outlet — you should estimate
sales volume using this data.

• Use the query feature in Access to join the tables and calculate aggregate sales for each product. Do not worry

about the location of the sales—you are interested in total sales only.

• You are running the sale in the Fall. Given the seasonality of some of the products, you should look at sales from the

start of September to the end of December time frame only. Moreover, since customers’ preferences change over
time, you should consider only the most recent year’s sales.

[June7]

Okay, sorry, I thought the query was more complicated and included multiple tables with 1-to-many relationship. I should have looked more closely. SELECT query would not modify the original tables.

As Paul said, you have too many fields.

I don't see Width and Depth data in the example output.

SELECT Sales.Product_ID, Item, Price, Sum([Quantity]*[Price]*[Gross_Margin]) AS SumProfit FROM Products INNER JOIN Sales on Products.Product_ID = Sales.Product_ID WHERE Transdate BETWEEN #9/1/2011# AND #12/31/2011# GROUP BY Sales.Product_ID, Item, Price ORDER BY Sum([Quantity]*[Price]) DESC;

[kevinhadrice]

Okay, so I've removed width and depth and I currently have

product id
item
price
Quantity
gross margin
sales (Q*price)
profit (Q*price*GM)
transdate

Sorry, this is literally my first time opening access. What do I need to do to not modify the original tables and group the sales of each item together instead of having multiple rows for each item because they're in different locations/dates?

SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.ITEM, PRODUCTS.PRICE, SALES.QUANTITY, PRODUCTS.GROSS_MARGIN, Sum([Quantity]*[Price]) AS Sales, [Quantity]*[Price]*[Gross_Margin] AS Profit
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
WHERE (((SALES.TRANSDATE)>=#9/1/2011# And (SALES.TRANSDATE)<=#12/31/2011#))
GROUP BY PRODUCTS.PRODUCT_ID, PRODUCTS.ITEM, PRODUCTS.PRICE, SALES.QUANTITY, PRODUCTS.GROSS_MARGIN, [Quantity]*[Price]*[Gross_Margin]
ORDER BY Sum([Quantity]*[Price]) DESC;

[June7]

Try the query I suggested.

You can switch the query designer to SQL View and copy/paste the statement I provided. Then switch back to Design View to see the grid.

[kevinhadrice]

Try the query I suggested.

You can switch the query designer to SQL View and copy/paste the statement I provided. Then switch back to Design View to see the grid.

Hi, so I actually found out how to group it.