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

    Are multiple detail layers possible on one report, each with separate control source?


    I'm sending you zipped files of two Word documents which show the Report and Design views, respectively, of my work so far on the Soda Tally Annual Inventory report. They explain what I hope to do. I sent this at first to the general Access forum, and only later realized I should have sent it to Reports. Oops!
    Attached Files Attached Files

  2. #2
    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 Jerry Call View Post
    I'm sending you zipped files of two Word documents which show the Report and Design views, respectively, of my work so far on the Soda Tally Annual Inventory report. They explain what I hope to do. I sent this at first to the general Access forum, and only later realized I should have sent it to Reports. Oops!
    So I'll repeat what I posted on your other thread that you did not answer. . .

    Something tells me you have a data problem. Noting you have "tblSodaTallyNorthM", did you do seperate tables for each store?

  3. #3
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    So sorry about screwing up the target. Hopefully, though, the screenshots came through. To the point: Yes, there will be separate tables for the four reports. Ideally, I would like to print each report so that they all come out of the printer on the same continuous series of pages. I suspect the only way this can be done is with VBA, and that's scary.

  4. #4
    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’d like to avoid having to run separate reports for South Machines, North Store, and South Store. Is there a way to stack all four of these groups up one above the other on this single report? What has me stymied is that each successive layer would have to have its own separate control source. Fortunately, the data contents in all cases are identical, so the same Detail setup would work for all four groups.
    There is probably a number of work arounds to make it work. A report with 4 subreports or a union query with grouping But you should probably step back and look at your table design.

    By having 4 identical tables you are storing data as tables instead of storing data in tables. Wouldn't it make sense to have one table with a field to identify the store?
    With one table you have only one report with one source query and group your data on the StoreID.

    edit: in reality you would have more than one table because you would have one table for stores , another for machines, another for products, etc.
    You may also have to plan for unit prices changing over time, etc.

  5. #5
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    There are operational reasons at inventory-taking time in October for maintaining the separate tables.
    So tell me more about union queries with grouping. I don't believe the subreport feature in Access is very well suited to my present situation.
    Don't worry about unit prices changing. I have a master table for each of the four groups, and when I enter a changed price in the master it's automatically propagated to all four subordinate tables. Furthermore, we don't have to maintain price change information throughout most of the year. In September, after we've received our most recent orders we enter the current data for each item, including its price, into my app. That gives us what we need for the annual October inventory. Then we wait until next September to review the list.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Jerry Call View Post
    There are operational reasons at inventory-taking time in October for maintaining the separate tables.
    I'm with moke123. As a general rule, you design the database with proper normalization then create interfaces that make sense operationally. You don't let operational considerations drive table design (again, as a general rule). In this case, I would like to hear the operational reason for separate tables. I'd have one, and simply filter for the appropriate store. Your report would likely be based on that single table and grouped on store.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    Well, let's consider what I need. It might be that I could do it with only the four master tables, but I would need a lot of help.

    I now have designed multiple tally sheets which contain the item lists for Soda, Coffee, Snacks, and Supplies, each of which has four subdividions: South Store, North Store, South Machines, North Machines. At each "location," using the appropriate tally sheet, the person doing the recording writes down the count for each item. That information has to be preserved separately initially, although it will later serve as the basis for various consolidated reports. So, for example, I'll get a set of sheets for "South Store Soda", another for "North Machines Coffee," and so on.

    I've invented what I think are fairly clever (for a beginner!) functions that make it very easy to record the counts from the tally sheets brought in from the site. (That is, the counts are hand-copied; the tally sheets themselves are printed with the list of all items relevant to each category). That data has to be preserved separately, although it will later be amalgamated into final reports. My current design allows me to preserve each "stamped" type of data in a separate table.

    So I want to end up with the data for South Store Soda, North Store Soda, South Machines Soda, North Machines Soda--and so on for all four of the main categories. My present design does that adequately, although it seems I may have run afoul of the rules of regularizing. But this would have all worked fine if not for the fact that having separate tables as data sources make it (perhaps?) impossible--or, I gather, at least difficult--to do the final reports. Were it not for that I wouldn't have bothered you in the first place.

    So help me understand how I might rebuild this aspect of my app. The master tables all have a Location field. It's left blank in the master table, but when I make an entry in the master table my code whisks all the various location info. off to each of the four satellite tables, affixing the appropriate "brand" in the Location field. For example: Soda Tally North Store, Soda Tally South Store, Soda Tally North Machines, Soda Tally South Machines. etc.

    Thus, it's easy to do the necessary separate reports for the four satellite tables because the relevant counts pertaining to each location are already separated out. This seems very logical to me.

    Now, how might one come at this in another way? Somehow you have to achieve the same results. Well, in my mind, maybe instead of having separate tables in which every field is the same except for the item counts, maybe this could be done another way?
    But I don't exactly see how to do it. My present plan is to use each satellite table as the data source for each report (section). If only Access would allow for the possibility of stacking up sets of data from different sources as levels in one report, we'd be home free.

    Worst case scenario is I could just run each report separately. Takes more paper but still gets the data printed out and usable. What would be nice if Microsoft (along with fixing some of the wretched aspects of Access like the infinitesimally tiny type in the Property sheets) could give me the possibility of telling it: Run Report 1, then print Report 2, 3, and 4 using the same sheets of paper, so that if Report 1 ends in the middle of a page, start Report 2 on that same page and, just keep going as part of the same print job.

    It appears individual reports can be converted to pdf files. Perhaps in Adobe if I could make each separate report part of a packet of items that would print out as a whole. I've had only a little experience with manipulating pdf's. I normally just make them to send individual documents to other people. But since we do all of this only once each year, if I knew how to combine the items in Adobe, that might be one solution. Any hope of that?

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Thus, it's easy to do the necessary separate reports for the four satellite tables because the relevant counts pertaining to each location are already separated out. This seems very logical to me.
    Its more logical to do it from normalized data.

    "Select * from SomeTableName" - gets all records
    "Select * from SomeTableName where storeNumber = 1" - gets all records from North store (StoreNumber 1)
    "Select * from SomeTableName where storeNumber = 2" - gets all records from North Machine (StoreNumber 2)
    "Select * from SomeTableName where storeNumber = 3" - gets all records from South store (StoreNumber 3)
    "Select * from SomeTableName where storeNumber = 4" - gets all records from South Machine (StoreNumber 4)

    "Select * from SomeTableName GroupBy StoreNumber"

    there are tons of video tutorials and articles on grouping and sorting in access reports.

    https://support.microsoft.com/en-us/...2-4a23ac0fdbf3

    https://youtu.be/rIEFbNIHoTA

  9. #9
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    I'll look into the sites you mentioned. Sorry I haven't had time to spend enough time on this as a beginner to learn all this stuff. I did get Kirt Kershaw's Access series on cd-Rom, and it's superb for what it covers, but alas, I haven't yet been able to find an explanation of the kind of thing you're talking about. So let's close off this thread for the present. I'll let you know how it comes out.

  10. #10
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    Vici!!! The multiple SELECT statements with UNION ALL did the trick. Also finally figured out how the group/sort controls work.
    I know you don't approve of just screenshots rather than db's, but since I've finally got my app brought to successful
    completion I can't resist showing you what the Soda report looks like. Of course, in the real world there
    will be more items when we finish the tally this year. This is just to show you what the report will look like.

    Thanks for your patience. The learning curve is steep and painful for me, but bit by bit I'm learning a little more all the time.

    Soda Group Tally Report.zip

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Congats, Glad you got something working.

    The learning curve is steep and painful for me, but bit by bit I'm learning a little more all the time.
    That's a common refrain around the boards.
    Now put it in a safe place, take a copy and start over again. Try to better what you have. Normalize, consolidate, try new methods. If it works keep it. If it doesn't, try again.
    Chances are you're going to want to add functionality to it in the future.
    Good luck.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-16-2020, 12:36 AM
  2. Replies: 3
    Last Post: 01-11-2020, 06:30 AM
  3. Replies: 3
    Last Post: 05-03-2014, 03:26 AM
  4. Report Control Source Manipulation
    By justphilip2003 in forum Reports
    Replies: 15
    Last Post: 05-19-2013, 10:06 PM
  5. Replies: 5
    Last Post: 05-18-2011, 11:02 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