Results 1 to 8 of 8
  1. #1
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85

    Extracting calculated total fields from one report and placing them in another report

    CGVending.zip

    Here's my latest database for my C & G VENDING app. It's almost complete. Just a few details remain to be worked out. Here's one of them.

    Begin by bringing up the main screen and printing two reports. The first is the Soda Tally Report (green label). The second is Annual Summary Report (light orange label).

    All data in these reports is for demo purposes only. No actual or confidential information is contained in them, so you have the run of the entire app.

    The Soda Tally Report itself is ready to go, along with similar reports for the other three groups (Coffee, Snacks, and Supplies).
    I don't need any help with the Soda Group Report or its identically structured reports for the Snacks, Coffee, and Supplies groups.
    The reason I'm showing you a typical one of these reports (for the four locations of the Soda group) is that I want to extract
    group (=location) totals from each report (=16 groups in all, printed on four separate reports, each with four subgroups)
    and pour them into appropriate fields in the Annual Summary Report.

    Confession: I cheated setting up the present Annual Summary report by creating a table linked to a form, into which I manually typed in dummy numbers.
    So all of the present data was just typed in by me, not magically calculated and entered by Access. The first two column-blocks on the left
    are what concern us here. To their right is a section of summaries which need not detain us here, since they're just extracted and consolidated
    from data in the two columns on the left. It's those 16 group totals that I'm after.

    I want the Summary report to be a separate single item, not glommed onto the tally reports. What I need to learn how to do is how to
    reference each group total on a separate report. Specifically, in the Tally reports the Unit Cost field is divided by the NoInUnit field, producing
    the dollar amount for a single piece (such as a can of pop). Then that single piece price is multiplied by the count (tally) for each item.
    Thus, with Root Beer in the Soda Tally North Machines, the unit cost ($45.345) is divided by 24 (number of units in a full case) to
    determine the price for a single can. Then that's muliplied by 25 cans counted in the inventory to get a total value for Root Beer of $47.234.
    (I need three decimal place accuracy for this report).

    I'm NOT concerned with any of the Grand Totals at the bottom of the Tally reports for purposes of the Annual Summary. I'll get them
    the "normal" way by calculated fields within the Annual report--once I have the 16 chunks of group totals data.

    Thus, in the Soda Tally report, teach me how to transfer the $149.912, $276.000, $296.309, and $389.306 totals from the Value column
    into the corresponding slots in the Annual report.

    I know how to manipulate "regular" data from one place and get it to another. But I think calculated fields like these are a different critter,
    and presumably require different techniques to move them around.

    Perhaps one could give a distinct name to each group. For example, in the Soda group, we could have SodaNorthMachinesTotal, SodaNorthStoreTotal, etc.
    However it's done, I want Access to do the following:



    Look in the Soda Tally Report and capture each total for the four groups. Then transfer that information into the control sources for each of the corresponding
    fields in the Annual Report. Repeat for all the other groups.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, assign a group identifier, either by records in a table or calculated field, and build an aggregate report.

    Instead of 5 soda tables why not 1 table with another field for the group identifier? Same for snacks and coffee and supplies. Why are there multiple tables with identical structure?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    I've gone the rounds with others about the 5 tables vs. 1. Trust me: This is the way that works for me. To do what I need to do to collect the data for each group I have to be working with a table, not a view (which can't be updated).

    But on to the "aggregate report," which is what I need us to focus upon.

    Remember, I'm a beginner with Access. How do I "assign a group identifier" to the group totals field for the Soda Tally North Machines location?
    Then how to extract its value and move it to another report?

    Could this be as simple as a copy and paste operation?

    Sample vba code for one item? Reference to discussions of aggregate reports?

  4. #4
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    Hold it, folks! I believe I found a reference that shows how to do this. So don't respond for now.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Jerry,

    I question the tables design, as did June. What if you get another Product? Or add a location? Seems you're back to designing/copying structure and making another table(s). I don't mean to dissuade you from your report issue, but your design jumps out to be questioned.
    I notice there is nothing in your database relationships window.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by orange View Post
    Jerry,

    I question the tables design, as did June. What if you get another Product? Or add a location? Seems you're back to designing/copying structure and making another table(s). I don't mean to dissuade you from your report issue, but your design jumps out to be questioned.
    I notice there is nothing in your database relationships window.
    You are not alone.

    Original Post https://www.accessforums.net/showthread.php?t=81509

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks Moke. I should have checked to see the OP's posts. Always nice to see a link saying --this is continuation of XXX.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean by can't update a view? Why would this be an issue if you had 1 table?
    Actually, 16 tables could be 1 table.

    Still not convinced you need multiple identical tables, especially since now you want to combine their data for report output.

    If you want to use wildcard in UNION then make sure fields are all in the same order in table design for all 4 tables, which you seem to have done.

    Now calculate a field in the UNION that can be used for grouping in aggregate query or in report design (which would be a field in 1-table structure):

    SELECT *, "TallyNorthMac" AS Cat FROM tblSodaTallyNorthMachines
    UNION SELECT *, "TallyNorthStore" FROM tblSodaTallyNorthStore
    UNION SELECT *, "TallySouthMac" FROM tblSodaTallySouthMachines
    UNION SELECT *, "TallySouthStore" FROM tblSodaTallySouthStore;

    Don't bother with ORDER BY in query because report design will control order.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 09-17-2019, 08:38 AM
  2. Replies: 6
    Last Post: 05-25-2018, 09:53 AM
  3. Summing calculated fields with DSum in a report
    By Carbontrader in forum Reports
    Replies: 3
    Last Post: 01-13-2017, 04:47 PM
  4. Replies: 5
    Last Post: 12-22-2016, 12:34 PM
  5. Replies: 1
    Last Post: 07-25-2013, 01:20 PM

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