Union query Problem?

[Access]

I have some tables whose records I gather using a union query as below:

Suppliers: ID, name
Customers: ID, name
Employees: ID, name

So the union query has two columns, ID and Name.

The necessity is to have a lookup in a table holding mail receipients, that
can be either out of the company (suppliers, customer) or internal
(employees) or both.

All lookups in other tables are made using the ID field. Here in the union
table the ID field is not unique, thus I can't enforce RI cause IDs in the
union come from different tables, so when using the union query as a lookup
on the receipients table, if I select a record that its source is different
than the first table in the union query, if a record in the second table has
an ID that the first table has, it shows the first's tables name.
eg.

Source ID Name
-----------------------------------
Customer 1 abc
customer 2 def
supplier 1 ajk
Supplier 2 mbl
Supplier 3 ikl

If I select 'abc', it correctly saves id=1 and displays 'abc'
if then in another record, I select 'ajk', it saves id=1 BUT displays 'abc'
instead of 'ajk'
If then I select 'ikl' it saves and displays correctly 'ikl'

The issue here is that I wan't to be able to select from the union query,
but in the same time have RI with each of the union query's tables to the
receipients table, so that if eg a customer has correspondance, it cannot be
deleted from the customers table.

 

[Ken Sheridan]

I think you'll need to amend your logical model to handle this scenario.
What you have here is really a Class 'people' with 3 Sub-Classes Suppliers,
Customers and Employees. A class/sub-class is modelled by a one-to-one
relationship. Each sub-class shares the attributes of its (super) class,
i.e. Name in this case, but the attributes of each sub-class are not shared
by the (super) class or by the other sub-classes.

Consequently the way to model this would be to have a People table (or
whatever you wish to call it) with columns for the Name and any other
attributes of a 'person' you are recording. The primary keys of the 3
subclasses would also be foreign keys referencing the primary key of People.
From this it follows that while you can use an autonumber column as the
primary key of People you can't for the primary keys of the 3 sub-classes.

The Recipients table would thus reference the People table and there would
be no difficulty in enforcing referential integrity.

Decomposing each of your existing 3 tables to create the people table would
not be difficult. First you'd create the People table by appending all the
names and other relevant attributes to it from the other tables with 'append'
queries. Using an autonumber column for PersonID its values would be
inserted automatically of course. You might need to edit out any duplicate
rows manually after this. You'd then add a long integer number foreign key
column to each of the 3 original tables and by joining them to the people
Table in 3 'update' queries on the columns you've previously appended to it
you'd update the new foreign key column to the value of PersonID in the
matching row of the People table. Finally you'd delete the redundant columns
in the 3 original tables (the ones now in People) and make the new foreign
key column you created the primary key of each table. When you create the
relationships between them these will be created as one-to-one relationships.