Hi All,
I have an Access DB that I'm building for a retail chain of stores. I have a table of the items (item#, description, etc.) and a table called StorePrices for each item's price, cost, etc. in each of our 29 stores. The StorePrices table lists the cost, which is a cost per case, and it also lists the caseQty. I have created a query that lists each item's StoreID, Price, Cost, and CaseQty. It also has two columns to calculate the cost per item and the profit margin per item. Here is the query:
This works fine, but what I ultimately want, and can't figure out how to do, is to summarize the data by product group (Group1 in the ItemList table) Product groups are unique IDs that correspond to our various product groups, like Food, Candy, Magazines, etc.. So I'd like to show an average Price, average Cost, and average margin per group.
In addition I plan to create a query that shows the same info as above, but per store. So ultimately it will be something like:
Store Group AveOfPrice AveOfCost Margin 101 Beverage $ 3.00 $ 1.50 50.00% 101 Magazines $ 4.00 $ 3.00 33.00% 102 Beverage $ 3.25 $ 1.75 46.15% 102 Magazines $ 4.25 $ 3.25 23.53%
I know I need to be using a totals query, but I haven't figured it out yet. When I try to run the query as a totals query I get the error, "Division by zero".