Relationships among 3 tables

[Guest]

I have three tables - one for each location. I guess my first question is
whether or not I need three separate tables. So each table actually has all
matching fields. I've created a relationship among all three, but if I only
enter information into two of the tables the query I created won't show the
information. However, when I go and enter information into the third table
then the query shows that information. I am confused because I won't always
enter in information for each table. Sometimes it will be only one table,
sometimes two tables, or all three depeding on whether or not the customer
has multiple locations. Any advice? Would like to avoid going to far down
the wrong path and learn I have to start over. Thanks.

 

[Douglas J Steele]

Since you're presumably interested in the same information about each of the
3 locations, the answer is probably that you shouldn't have multiple tables.
You should add a Location identifier field in your table, and have only one.

 

[Rick B]

You should have all of this in one table. You need to add a field to
indicate the "Location" for each record.

You will then be able to include or exclude data based on the location field
entry in your queries.

 

[SusanV]

What if another location is added later? You'd have to create a whole new
table, queries etc - lot of work. You ought to have one table for common
data - including the 3 locations. Use a location code to differentiate
between them.

 

[Guest]

At present you are doing what's known in the jargon as 'encoding data as
table names'. This is incorrect because in a relational database data should
be stored as values at column positions in rows in tables and in no other way.

You in fact need two tables. One which contains all the data from your
existing three tables and has an extra column Location. The other table is
Locations which is just a list of the three locations in a Location column,
which is defined as its primary key. The Location column in the main table
is a foreign key (indexed non-uniquely, i.e. duplicates allowed) which
references the primary key of the Locations table.

You create a relationship between them on these two key columns. In the
relationship enforce referential integrity and cascade updates. That way no
invalid location can be entered into the main table and if you change a
location name in the locations table all rows which reference that location
in the main table will have their location names changed to match
automatically.

The alternative to using the location name as a 'natural key' would be to
have an autonumber LocationID column in Locations as its 'surrogate' primary
key and a Long Integer number LocationID column in the main table as its
foreign key.

Ken Sheridan
Stafford, England