Results 1 to 8 of 8
  1. #1
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93

    Total qtys of like items in an order report

    I have been trying for the last few years to do something which seems really, really simple and common, but cannot figure it out. I really wish Access could become a bit more user-friendly for something as simple as this.


    Here is my issue. I have a very simple report, an order report. An order sometimes may have the same item ordered more than once. Instead of showing an item occurrence more than once, I want the item to appear once, and total the quantities into one sum. Simple right? Well, not for me.
    I have tried sorting by item (which works), but then set the sorting to total by Quantity. What it does is give me a count of the number of occurrences of that item, it does not total the quantities, as I asked it to.

    So, how do I get the report to total "like" products, based on their quantities ordered?

    Thank you!!

    P.S. I have attached screen shots of my query, report, and report grouping settings.
    Attached Thumbnails Attached Thumbnails report setting.JPG   Order Query.JPG   sorted report.JPG  

  2. #2
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    Better report image attached...Click image for larger version. 

Name:	sorted report.JPG 
Views:	9 
Size:	35.0 KB 
ID:	26685

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Are you suggesting that in your business process---specifically Create an Order-- you don't get/record the Quantity?
    Please tell us step by step (with example if it helps) how taking the Order process works.

    Also, I recommend you get the Quantity and the Current Per item Price and record same in your OrderDetail/LineItem record.

    Good luck.

  4. #4
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Typically, when an item is added to an order that already exists and is still open, the quantity for that item is added to the existing item - not creating a new line item for the order. This would solve your problem at the source - not requiring a 'fix' to group them together on your report.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Here is one way: On your Group on Item options, select With Header Section and WITH Footer Section. Move the Item field from the detail section to this new Header section and it should only show once now. For totals, create a new hidden field in the detail section of the report called QtySum, in the control source put = [QtyOrdered] or whatever the name is of your Quantity field and choose the option in that field properties for Running Sum.(tip you can leave it visible to see what it shows while testing, then make invisible when you sure it looks right). Now where you have that total field that shows 3 or in the Item Footer section, add another field called qtyTotal and as control source put =[qtyOrdered] or whatever name you give to the hidden field that is summing the quantities.

    Also you can set it to do a running sum over group or whole report so do group so it just sums up each item.

  6. #6
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    I think my problem is that my quantity field is set up as a text field, preventing mathematical calculations. I'm afraid to change the field to number at this point. I think I would lose my data.

  7. #7
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Add a new numeric field to your table. Run an update query that will update the new numeric field with the value of the text field. Delete the old text field. Change the name of the new text field to what the old text field was. Of course, BACKUP first.

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Did you try adding that field to the detail and using Running sum? Make it visible and run the report and see if it adds your quantity correctly. Reread post 5 again for the details.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-07-2016, 05:02 PM
  2. Replies: 3
    Last Post: 01-18-2015, 06:05 PM
  3. Replies: 1
    Last Post: 03-10-2013, 12:49 AM
  4. prefilling items into an order form
    By syscoandrew in forum Forms
    Replies: 5
    Last Post: 09-25-2011, 12:27 PM
  5. Replies: 1
    Last Post: 10-28-2009, 12:44 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