Row calculations in Access

[doorok]

I'm keeping a table of numbers products sold daily by different categories. I want to be able to calculate the sum of all the products in all categories sold by day, month, and year. Three calculations. Sales are entered into each category daily by date through an access form. The data is stored in a table by date and category. How would I do the calculations in a query or in a report.

Thank,
You

 

[scriverb]

Here is a test scenarion with the SQL code to rollup your figures.

Table: tblSales
Fields: Category(10), ProdID Text(10), SalesAmt Number(Dbl)

Query SQL named qryProdIDSales:
Select A.Category, A.ProdID, Sum(A.SalesAmt) as TotalSalesProdID
FROM tblSales as A
GROUP BY A.Category, A.ProdID
ORDER BY A.Category, A.ProdID;

Query SQL named qryCategorySales:
Select A.Category, Sum(A.SalesAmt) as TotalSalesCategory
FROM tblSales as A
GROUP BY A.Category
ORDER BY A.Category;

Query SQL named qrySales:
Select A.Category, A.ProdID, A.TotalSalesProdID, B.TotalSalesCategory
FROM qryProdIDSales as A INNER JOIN qryCategorySales as B ON A.Category = B.Category
ORDER BY A.Category, A.ProdID;

You can run the first two queries seperately to get individual totals and then run the third one to combine the totals into one row.

Let me know if you have any problems here as I did this freehand without testing.

Bob Scriver