Results 1 to 2 of 2
  1. #1
    Tambe257 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Mar 2017
    Posts
    28

    Proper Grouping Of A Totals Query

    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:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	29.3 KB 
ID:	27984

    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".

  2. #2
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    You will want to use an Aggregate query; when in query design view look for the sigma symbol (like a big E); group on store, group on group, then option Price and Cost for average.

    Margin is a different issue - one can't really average a percent from a math perspective - though it might be good enough for your situation - in which case add that field too,,,, but from a math perspective what you need to do in a separate query is sum all costs, sum all prices and then determine the margin off those.... then join this value back into your record set or in your report...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query detail totals vs. Summary Totals do not match?
    By aellistechsupport in forum Queries
    Replies: 9
    Last Post: 01-15-2016, 11:36 AM
  2. my totals query is not grouping
    By josekreif in forum Queries
    Replies: 3
    Last Post: 09-08-2015, 07:53 AM
  3. Replies: 3
    Last Post: 06-27-2013, 10:18 AM
  4. Need help with totals/grouping
    By Leon_SF in forum Queries
    Replies: 3
    Last Post: 02-08-2012, 09:51 AM
  5. Reports Grouping Totals in details
    By slatterie in forum Reports
    Replies: 1
    Last Post: 01-27-2012, 07:10 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums