Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unanswered: Unique Cross Table query!

    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.

  2. #2
    Join Date
    Oct 2003
    Posts
    61

    Re: Unique Cross Table query!

    Originally posted by uk1
    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.

    Create a union query which looks like this:

    Select distinct food_orders.customer , "food-orders" as table
    from food_orders

    Union

    Select Disctinct Travel_expenses.customer, "travel_expenses" as table
    from travel_expenses

    Union

    etcetera for all tables.

    And after that create a crosstab on the union query which will give you your results
    Nobody dies a virgin because life screws us all!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •