Creating a query for multiple tables in one database

[Guest]

I have created a simple database for our church cemetery, one table tracks
the plots and their useage (ie: someone interred, reserved or vacant); one
table showing those people who are interred in which plot (can have multiple
people in plot depending or burial process); and a table showing who reserved
what plot. I now need to print a report on all the plot particulars, is
what's it useage, who is interred there if anyone and who reserved the plot.
I do not know how to make a query work across multiple tables

 

[Michel Walsh]

The basic query I imagine is to bring the table having all the plots, then,
one by one, add a table and JOIN it with the first table, but edit the join,
select the option that says to keep all the records from the first table
(the one having all the plots). Repeat for all the table that mentions a
plot.

Once done, bring the required fields in the grid, of the editor, and save
the query. Use that query as base for your report.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I am not certain I know how to execute what you have suggested. All Tables
have the common identifier of Plot ID. I tried to creatre a query from the
Plot Table and then appended the People Table, but all I got was duplicates
inside the People Table, so I know I am doing something wrong. The Plot
Table has the following basic info: Plot ID (auto number 4 characters), Plot
number from drop down list, it's useage (ie: interred, reserved, vacant) and
an image of the Grave Marker. The People and Reservations are all tied to
the plot via the Plot ID and yet have their own auto number ID assigned as
well for each table ( ie: In peopel Table, I have a People ID (auto number)
and the Plot ID as the link o the Plot Table, etc.

Galen

 

[Michel Walsh]

When you 'append' the tables, I assume you bring the second table in the
query, and then, make the JOIN? to make the join click on the field PlotID
from the Plots table, and drag it over the PlotID field of the second table
you just added to the query. A line will appear between the two tables.
Click on it, a small dialog with 3 choices will be presented to you, select
the choice that say to keep all records from table Plots.

Bring the fields you want to see, in the grid. You should not have
duplicated People anymore, unless they were originally duplicated in their
initial table, or unless they are related to more than a single plot. The
JOIN we created force a match between the plotID values, so, if someone is
related to just one plot, that someone should appear just once, too, with
this combination.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Mr Walsh:
Got it. I had the relationships wrong and once I corrected that, it all
worked as you said it would. I knew it was something simple, just didn't
know what.

Thanks you.

Galen