Relationship and grouping problems in report with multiple tables

[Jerry Call]

I'm back with my report problem, but this time with some progress in the meantime. I have four tables with identical structure but differing data. They all have a primary key, ItemName. All tables also contain a Location field, such as Soda Tally North Store, Soda Tally North Machines, etc.

I want to create a report which includes all four tables and groups the results by Location. I've figured out how to use the Report Wizard. But so far the only data that comes out on the report is that for the first table in the query.
Almost certainly it's a problem having to do with relating the four tables properly in the Relationships section. Here I haven't got it figured out yet. I started by linking the ItemName primary field of all reports. I ended up with a 1 to 1
result. That didn't work. Then I tried relating Table 1's Item Name to Table 2's Location field. That didn't work. So I need some help.

What I've read says you want to link the primary field of Table 1 to a "foreign key field" in Table 2. The examples I reviewed showed a slightly different situation from mine. They had two tables with different data and structure, such as


Employees and Manufacturer. Employees had, of course, an EmployeeID primary field. Manufacturer had nothing like that, so they added an EmployeeID field to Manufacturer. Voila! The foreign key. Now they had something to link.

In my case the Location field is what I want to group on. But all four of my tables have the ItemName primary key. So presumably there's no need for a "foreign key" here. I think the trouble must lie in how to set up
the relationship between the four tables. One to one? One to many? It appears that one to one isn't working. How does One to many work?

I want to produce the following effect: When I run the report I'll see all extant data in all selected rows in the query, and the data from each table will be grouped by Location. My request is the same as Commander Picard's: Make it so!

[pbaldy]

I still think the data should be in one table. The workaround is a union query which normalizes data.

SELECT *, "North Store" As Location
FROM NorthStoreTable
UNION ALL
SELECT *, "South Store" As Location
FROM SouthStoreTable
...

[Jerry Call]

There is simply no way I can collect the various tallies I need to record separately if they're all in one table. Gotta have the separate tables. But let me experiment with your workaround.

[Jerry Call]

Hold it! I think I may be able to implement your one-table concept after all. What was bothering me was how to collect and record separately the information from the four groups.
But eureka! All I would need to do is modify the forms in which the statistical info. is recorded to have fields matched to the particular one I'm recording. Thus, when in the
North Store Soda Tally form, I would make sure that the data goes into the appropriate target spot. I guess I would probably have to make a view as the record source.
But this might be going in the right direction. You must keep in mind that you have lots of experience, and so you tend to take things for granted that I haven't done before.

[pbaldy]

Well, more accurate than "I would make sure that the data goes into the appropriate target spot" would be that you make sure you record the appropriate store with each record. To view records you'd either base the form on a query that included a criteria on store or use this to open it filtered:

http://www.baldyweb.com/wherecondition.htm

With all the stores in one table (with a field to signify store) you can pull any combination of one or more stores into a report.

Not to muddy the water, but do you also have fields for items? That's also a normalization issue. It would be like having fields for items you sell, "hammer", "pliers", etc.

[Jerry Call]

OK, so far I believe I see how to do it. In the report I want to be able to sort by the four Locations (Soda North Store, Soda South Store, Soda North Machines, Soda South Machines). To accomplish that I now have four Location fields in the master table:
Location1, Location2, Location3, and Location4. Using the default value function, each new table record will now be automatically populated with all four locations. Then, in order to accomplish the tally data entry, I'll make four views, and those will
serve as the record source within the four corresponding forms in which I enter the tally data for the four groups. In each case all the "standing" data, including--since you asked--ItemName, the primary field, will be identical. Thus, there will be
one, and only one, record for ItemName = Aquafina, another for Root Beer, etc. So no need to worry about normalization.

Once I have the data entered I can use the Location fields in order to group the report. The only concern I have about this--and here's where I need some instruction from you--is when it comes time to construct the report. I presume that the wizard will let me
pull in all four views, since earlier it allowed me to pull in all four of the former separate tables (now deleted). So far so good. But with the four views, what about grouping by Location? Remember that the four Location fields now have different names--Location1, Location2, Location3, Location4. So Location1 is always Soda North Store, Location2 is always Soda North Machines, and so on. Ah, but those require four different "Location" fields. Will the wizard allow me to group viewSodaNorthStore by Location1, viewSodaNorthMachines by Location2, etc.? I suspect this could be a tricky process, which probably will require working through the wizard in a certain order. Are there enough separate levels allowed by the wizard?

[moke123]

Remember that the four Location fields now have different names--Location1, Location2, Location3, Location4. So Location1 is always Soda North Store, Location2 is always Soda North Machines, and so on. Ah, but those require four different "Location" fields.

There should only be one field "LocationID" and in that field should be the foreign key of your locations table.

It may be a good idea if you strip out any confidential info and post a copy of your DB with just enough info for it to work. We'd be better able to guide you that way.
Somehow I get the feeling that you used to do this in excel and are now looking to transition to access.

[Jerry Call]

I was afraid that might be the case. I don't have a separate Locations table. It appears I should have one. I'll make one and see if I can link it up.
Then we'll go back to the issues with the report builder.

[pbaldy]

The sample moke mentioned would probably help too. We're trying to visualize what you have, easier to actually see it.

[ssanfu]

Maybe you would post an image of your relationship window? Or better yet, post your dB....without sensitive data......

[Jerry Call]

Let's try it with words first. My tblLocations only one field, "Location", with the following records: Soda North Store, Soda South Store, Soda North Machines, Soda South Machines, plus corresponding ones for Coffee and Snacks. Let's just limit ourselves to Soda for now, because once I get that one right the other tables are analogous.

tblSoda, where all the rest of the records are stored, has fields for ItemName (=primary), Supplier, UnitType, NoInFull, FullUnitCost, FullUnitTally, PiecesTally, CumTally, and I just added Location. But I'm not sure if there should be a location field in this master table.
I leave it blank on the assumption that I need it to match up with the Location field in the Locations table. But that doesn't seem to work when I try to construct the query.

Attempting to make qrySodaNorthStore I pulled in all the records from tblSoda, all the records from tblLocations, and tried to connect the Location field in tblSoda to the Locations field in tblLocations. That produces no records.
So then I tried relating the ItemName field in the main table to the Locations field in tblLocations. Ah, but what kind of join, and how to make it? I told the design that I wanted to include only "Soda North Store" from the Locations table.
But the result set comes up empty. So I'm in the quicksand again. Show me how to make the query include all records from tblSoda plus "Soda North Store" field from tblLocations.

[Jerry Call]

Whoa! I think I've got it! The trick is to bring into the query all records in tblSoda and all records in tblLocations--BUT DO NOT RELATE the two tables! No link! All I have to do to get qrySodaNorthStore is bring in
tblSoda to the query (all records), then tblLocations (all records), but specify a WHERE clause for tblLocations: "Soda North Store." That does it.

Next, though, I have to figure out how to combine the four queries for Soda for report purposes. But first things first. No more needed from you until I get to the report end. But sweet success in the view!!! (I'm reminded of
Winston Churchill's comment: The Americans always manage to come up with the right solution to their problems--after trying everything else!" That goes for yours truly in this case).

[ssanfu]

Why would you have to combine the 4 queries for the report? Isn't that the table? (Well, I would still use a query)
You could have a query to select the records from the table for a given time period, say 2020, but the report would group.

If you wanted to to use 4 queries, create s Union query.



It would be helpful to see the relationship window or the dB to see what your design looks like


"Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.



This is what I came up with from reading your posts. I might also have a table for "Items"...... but I don't know much about your requirements.






Good luck with your project......

[Jerry Call]

I'm glad you have Velcro. I need that. Instead, take a look at my query setup for rptSodaCombined. It includes queries for all four tables with data, although you don't see them in this shot.
I construct each view from all the fields in the various tables, with the exception of FullUnitTally and PiecesTally, which are used in the forms in which I enter data and calculate a total number of pieces for each item.
In the queries I bring in each of the four tables and also the Location table. I do NOT relate the Location table to the others. The enclosed screenshot shows what my Wizard looks like. WhenReport Wizard for rptSodaCombined.zip run this produces
exactly what I want--except, alas, only for the first table. Location is there in just the right spot. All data there. All perfect. But I'm getting only the first layer. So perhaps we have to provide for multiple levels
in order to get all four views included in the combined report? How do we do that with the wizard?

[moke123]

I dont use the wizard as I think its easier to do manually'

Heres a quick example although I dont quite know what your intentions are.
Note to calculate things like full cases for example you would divide the invidual pieces by the number that comes in a case.
this doesnt include that but is just a guide as to how you may want to set up.