Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    My existing design is less complex than yours because it doesn't need to be more complex. I only need two basic tables, the one that includes every field except location, and a location table.
    In order to collect the variable information for the same fields from the four different areas I have constructed four queries for Soda data, each of which results in a combined data set with a different location.
    I feed Query1, for example, into a form in which I enter either number of full units, or individual pieces (example: 6 cases of Root Beer and 13 singles in the South Store). My form allows me to produce
    a running cumulative item tally (which is what we want) for each item in that location. Works like a charm, though it took me a long to figure out the machinery. So when I work through the records
    for Soda South Store, for example, all the data for the report is sitting right there in that query. Same for the other three queries.

    Right now, if I click on any one of the queries to open it, there I find exactly what I want to know about Aquafina, Root Beer, 7-Up, etc. in the South Store.
    So I have exactly what I want in the queries, and it wasn't too complicated after I figured out how to do it.

    What I need help from you on is (preferably) how to make the report wizard deliver each of these four query outputs into one four-level report
    grouped by location. At the moment the wizard allows me to import all four queries, each of which leaves out the two fields which were used
    in connection with calculations in the relevant form(s). And it allows me to specify Location as a grouper, as you see on my previous download.

    When I run the report, to my delight I see the first query results exactly as I want them to look, with correct data and the location field serving
    as a header. But to my distress, that all I see. The other three queries are AWOL.

    If this can be done with the report wizard, that would be my preference. But if we have to go to (ugh) vba, then you'll need to teach me
    how to generate that code.

    That fearful SELECT (AT YOUR PERIL) technology is very indimidating to me. I love that I can get exactly the four individual result groups I want
    by just constructing the queries, which is very easy. Perhaps it's possible to convert my queries into vba??

    So here's what we want.


    1. Soda South Store query output, grouped by location, as first "layer" in the report.
    2. Soda North Store query output, grouped by location, as second layer.
    3. Soda South Machines query output, grouped by location, as third layer.
    4. Soda North Machines query output, grouped by location, as third layer.

    Can you "make it so," as Commander Picard would wish?

    I'm OK with Velcro for now, but eventually we've got to get to Vici.

  2. #17
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The Word doc is of little to no help. A good dB design depends on the table designs and relationships (aka Normalization). So far, it has been like the 3 blind mice trying to describe an elephant.

    It appears like you have something to do with Sodas, but also Coffee and Snacks. Without knowing all about the requirements, we cannot do a good job advising you. I was once tasked with creating a dB to monitor Permits for one facility. After providing examples at different stages of development (and I was almost finished), the mucky-mucks decided to include two more facilities. If I knew they wanted a dB to track permits for 3 facilities, the dB design would have been very different. What they ended up with was the dB I finished, with different colors for each facility and obviously different facility names.


    You seem to have a dB but don't want to post it. Your decision, but without it,not much I can do, so I'm going to step out of the thread.


    Good luck with your project........

  3. #18
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    In your DB that I got from your previous post you have 20 tables which should only be 4 or 5 tables or less. As I said previously you do not store data as tables.
    You really haven't explained what your database is supposed to do. We can see its apparently soda machines but what is it your tracking? At one point you stated this is a once a year inventory.
    It would be really helpful to tell us exactly what your database is about. Are you tracking stock? sales? Whats the difference between a North Store and a North Machine?

    Quite frankly, you dont seem to be interested in doing this the right way which is going to doom you to continuously seeking work arounds.

  4. #19
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Since you seem intent on using the wizard, just want to make sure your aware of the group and sort on the ribbon. Much easier than wizard IMHO.
    Click image for larger version. 

Name:	ribb.png 
Views:	19 
Size:	13.6 KB 
ID:	42784

    and how that opens the dialog at the bottom of the report design

    Click image for larger version. 

Name:	bottom.png 
Views:	18 
Size:	7.0 KB 
ID:	42785

  5. #20
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    I told you earlier not to pay any attention to the db I sent you earlier. It doesn't reflect the very different state of the app now. Instead, pay attention to the screen shots I've sent you. I'll now be sending you yet another shot, which shows the form in which data is entered for one of the Soda locations.

    I've deliberately limited myself to the four Soda group locations, since the set for Coffee and Snacks have the identical structure. But since you insist on a detailed explanation of what I'm trying to do, here goes.
    This app has a single purpose: reporting the inventory on hand at vending machines in the Southbound and Northbound locations of two Interstate 57 rest areas near Pesotum, Illinois. The inventory is done every October.
    At inventory time someone counts the various items for Soda, Coffee, and Snacks in (1) the vending machines themselves; and (2) the adjacent storehouse. We want separate totals for each of the four categories
    for Soda, Coffee, and Snacks, plus, of course, at the end a grand total for the entire operation. The latter report is already in place, and needn't detain us here. What concerns us is how the app records the counts of each item.

    Worksheets have been prepared for each group. I'm enclosing a screenshot of one of them. As you can see there are two places for writing numbers: number of full units (whether cases, boxes, packages, etc.), and number
    of "singles." Suppose, for example that for Aquafina we have 5 full cases (24 to the case). That comes to 120. Then we have 16 singles. So the cumulative total = 136.

    I'm also sending you a screen shot of the form in which we enter this data into the app. As you can see, there are only two fields in which the user can enter data: the count of full units, and the count of singles.
    When the user enters 5 into the first screen the app multiplies 5 by the number in a full unit (24 per case) and places the result of 120 into the cumulative total field. Then we enter 16 into the singles field, and
    voila! my magic machine adds 16 to the total to get 136. Then bring up the next record and do the same. In the end we'll have all the data we'll need for the final report. So for Soda group we'll have four
    separate reports--South Store, North Store, South Machines, North Machines. Similar sets will be produced for Coffee and Snacks.

    Now the report I'm struggling with is the one that combines each of the four location data sets for Soda on a single report, but grouped by location. As you can see from the screenshot, all the data I need
    is right there in the queries that feed the four tally forms for Soda. So how to I get this onto one report?

    You say I seem determined to do this the wrong way. Not so, Velcro! I'm interested in Vici instead. But remember I'm a beginner. You insisted on a thorough explanation from me about my app.
    Well and good. But what I need from you is not criticism for stubbornness, but a similarly detailed explanation--that a beginner can follow--of the "right way."

    I was not aware of the Group & Sort group on the ribbon. It looks promising. Your photo is worth much, but I may be back soon for further explanation.
    Soda Tally North Store form.zip

  6. #21
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    Oops! Forgot to send you the Tally Sheet sample. Here it is.Northbound Store Tally Sheet for Soda.zip

  7. #22
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    As Steve has already indicated screenshots of forms do little to help us help you.
    If you insist on using a non - normalized structure your answer has already been provided. Either use 4 sub-reports or a union query joining all your tables.
    https://support.microsoft.com/en-us/...0-ad0a75541c6e
    https://www.youtube.com/watch?v=ImzrovfgBWU

    The example I posted should show you how normalized data should look in a Relational Database. Each related piece of data is stored in its own table (ie. Locations, Products, suppliers, etc.) and then in your Tally table you bring them all together using PKeys and fields for your counts. Generating reports becomes much simpler this way.

    I am curious whether you intend to use a blank database each year. No way to show year to year comparisons? What happens when you add/delete products? What happens when prices change?
    Have you given any thought to future-proofing?

  8. #23
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    We won't start with a blank database each year, since many of the products and their associated data including prices will remain the same.
    However, at the end of the year I'll erase all the tally counts from the app (of course, after saving the app with the previous year's data as a separate backup file for Access).

    For those products whose prices do change, we'll use the latest September invoices from our supplier to determine any new prices.
    Those can then easily be edited into the master tables for Soda, Coffee, and Snacks.

    Alas, it appears that the Velcro has torn loose without getting to Vici. Looks as though I've reached the end of the line with you folks about details of how to implement the annual reports I want. However, since you've given
    me two options (use 4 subreports or a UNION query joining all your tables) I'll now try to read up on how to do that with all the Access manuals I've got lying around.

    I purchased Kirt Kershaw's Access 2016 course and have been working through it. So far I haven't found the specific answer to this particular problem,
    but it may be in there somewhere. I also have half a dozen other Bibles/guides/you name it for access.

    I've had very little success just trolling the Internet. Tons of items come up, but they seem to be far away from what I need to know.
    Maybe I would be better just hiring a guru, but they're expensive, and earlier I did not have much success coming up with someone
    I felt to be both knowledgeable and who knew how to work with beginners. (Golly! If only I could hire Kirt! A shame he's accessible
    only through his fixed courses.

    Worst case this October: I'll just run all four reports for each group separately. That gives me all the information I need.
    I've already constructed a combination summary report which "works," except, of course that I'll have to manually
    enter the totals from each of the various reports into it, rather than having Access automatically pour in their data.

    Hopefully by next October I'll figure out how to let Access do all the work automatically.

  9. #24
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I'll now try to read up on how to do that with all the Access manuals I've got lying around.
    Start with the chapters on normalization. You'll be glad you did.

    Good luck with your project.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Relationship Problems (regarding tables and database design)
    By lugnutmonkey in forum Database Design
    Replies: 1
    Last Post: 01-04-2013, 12:33 PM
  2. Replies: 4
    Last Post: 12-12-2012, 10:21 AM
  3. Creating relationship for multiple tables
    By dave john in forum Programming
    Replies: 1
    Last Post: 09-02-2012, 08:18 AM
  4. Replies: 1
    Last Post: 03-31-2010, 11:57 PM
  5. Access report with 3 tables -grouping,sorting
    By kartechy in forum Reports
    Replies: 0
    Last Post: 07-22-2009, 09:07 AM

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