Help! Access SQL Query

[pinecrest515]

I have a question about aggregating numbers.

Suppose I have a SALES table that whose records are at an item transaction level (i.e. it displays sales by item).

I also have an ITEM table that contains item dept_nbr and sub_cat_nbr.


There is a DEPT table that maps dept_nbr to dept_desc, and a SUB_CAT table that maps sub_cat_nbr to sub_cat_desc.

Question: Suppose I want to find the total sales listed by department and subcategory descriptions. How can I best write the SQL? I don't want to see sales at the item level, only on the department and subcategory levels.

SALES table columns: sales_nbr, item_nbr, sales$
ITEM table columns: item_nbr, dept_nbr, sub_cat_nbr
DEPT table columns: dept_nbr, dept_desc
SUB_CAT table columns: sub_cat_nbr, sub_cat_desc

Would this work?

SELECT distinct
dept.dept_desc,
sub_cat.sub_cat_desc,
SUM(sales.sales$)
FROM
dept, sub_cat, item, sales
WHERE
dept.dept_nbr = item.dept_nbr
AND sub_cat.sub_cat_nbr = item.sub_cat_nbr
AND item.item_nbr = sales.item_nbr

[jzwp11]

What I would do first is create a query that gets the detailed information; something like this:

query name: qrySalesDetail
SELECT tblSales.sales_nbr, tblSales.SalesAmt, tblDept.dept_nbr, tblDept.dept_desc, tblSubCat.sub_cat_nbr, tblSubCat.sub_cat_desc
FROM tblSubCat INNER JOIN ((tblDept INNER JOIN tblItems ON tblDept.pkDeptID = tblItems.fkDeptID) INNER JOIN tblSales ON tblItems.pkItemID = tblSales.fkItemID) ON tblSubCat.pkSubCatID = tblItems.fkSubCatID;


Then use the above query in another query that sums the sales by dept and subcat.

query name: qrySalesSummaryByDeptSubCat
SELECT qrySalesDetail.dept_desc, qrySalesDetail.sub_cat_desc, Sum(qrySalesDetail.SalesAmt) AS SumOfSalesAmt
FROM qrySalesDetail
GROUP BY qrySalesDetail.dept_desc, qrySalesDetail.sub_cat_desc;


I was not sure if the number fields in your tables were key fields or not.

I have attached a sample database that I created that includes the pertinent tables and the queries mentioned above. Hopefully you will be able to adapt the example to your database.