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.