Make Relationship w/UNION-joined data?

  • Thread starter Thread starter DEF
  • Start date Start date
D

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
 
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.
 
I have two tables describing customers (names, addresses,
etc.), with different structures (although an SQL UNION
to join them is possible)
Click to expand...

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
 
Back
Top