Make Relationship w/UNION-joined data?

[DEF]

I have two tables describing customers (names, addresses,
etc.), with different structures (although an SQL UNION
to join them is possible) and a third table with orders
(pounds of peas, pounds of corn, etc.) from *both* sets
fo customers. I want to have a form which lets me look at
a customer's info and his orders at once, for all
customers. Later I'll want to make a report concerning
all customers and their orders.

I successfully make a UNION join between the customer
tables, yielding an "AllCustomers" table, and I'd think I
could make a a relationship between the CustomerID in the
joined tables and the Customer ID in the order table.
But, I see no listed fields in the "AllCustomers" joined
table, so I can't make the relationship I want.

Suggestions?

Many thanks,
--daniel

 

[John Nurick]

Hi Daniel,

You can't create relationships between a query and a table. But in a
query you can join a union query to a table.

 

[Bas Cost Budde]

You can't create relationships between a query and a table.

Oh yes! but you cannot enforce integrial referencity. Ur, Referential
Integrity.

 

[Tim Ferguson]

I have two tables describing customers (names, addresses,
etc.), with different structures (although an SQL UNION
to join them is possible)

Doesn't this suggest a subtyping solution? Have a table called
GenericCustomers, and two other called TypeACustomers and CustomersTypeB
with the distinct columns, and having each a 1:1 relationship with
GenericCustomers?

This is the only way to constrain the Orders to either type of customer
using the GenericCustomers table.

B wishes


Tim F