Unique Cross Table query!

[uk]

I have 20 tables of data, the common field between them being "customer number".

Some account numbers appear in one table , others appear in multiple tables.

I need to be able to see for each account number which table it appears in!
Note: I dont have a master customer number list - dont ask me why.

For example:

Table: Food orders

Table: Travel expenses

Table: Phone bill

Table: overdue payments

Some customers will appear in 'Food orders' and 'Phone bill', others will only appear in 'Travel expenses', others in all 4 tables. I need to see for each customer which tables they appear in.

 

[bat17]

You could join all of the tables in a union query, this will pull out all of the records but wont actualy tell you which table they comes from but you may be able to tell it from other data.
The only way that I can think of to return the table name is to create a query for each table and add another column to hold the table name - Something like tbl:"Phone bill" as a field heading should do it.

peter

 

[Russell Hauf]

Well, the quickest way I thought of was also basically a union query that is joined to all 4 tables. Say your tables are called:

FoodOrders
PhoneBill
OverduePmts
TravelExpenses

A query like the following will tell you what table(s) the customer number is in (My customer number is named CustNum):

• SELECT DISTINCT
  Z.[CustNum] AS CustNum
  , A.[CustNum] as FoodOrders
  , B.[CustNum] as PhoneBill
  , C.[CustNum] as Overdue
  , D.[CustNum] as Travel
  FROM
  ((((SELECT CustNum FROM FoodOrders
  UNION SELECT CustNum FROM TravelExpenses
  UNION SELECT CustNum FROM PhoneBill
  UNION SELECT CustNum FROM OverduePmts) AS Z
  LEFT JOIN FoodOrders A ON Z.[CustNum]=A.[CustNum])
  LEFT JOIN PhoneBill B ON Z.[CustNum]=B.[CustNum])
  LEFT JOIN OverduePmts C ON Z.[CustNum]=C.[CustNum])
  LEFT JOIN TravelExpenses D ON Z.[CustNum]=D.[CustNum];

What you end up with is the customer number in a column if it is in that table (otherwise it is blank).

Hope it helps,

Russell