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!