Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merge Tables (2003)

    I have to merge 10 tables together to form a single table with common field headings. Problem is that what I have is information for 10 years (LastName, FirstName, SSN, YYYYHours) There are some SSN's that are common to all the tables and would go through all 10 of the years, but there are many many SSN's that only appear once. What type of query do I need to build that will include all the data from all the tables so that the names and SSN's only appear once but any hours worked would appear to the right of the name/SSN?

    i.e. Last Name First Name SSN 2004 Hours 2003 Hours 2002 Hours 2001 Hours Etc. Hours

    I would post the DB but it includes our Employee's SSNs and I hate to share their information.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge Tables (2003)

    1. Create a union query to collect all SSNs.
    - Create a new query in design view.
    - Don't add any table.
    - Select View | SQL
    - Type the following SQ, substituting the correct table names:

    SELECT LastName, FirstName, SSN FROM <table1>
    UNION
    SELECT LastName, FirstName, SSN FROM <table2>
    UNION
    ...
    UNION
    SELECT LastName, FirstName, SSN FROM <table10>

    - Yes, you'll have to write it out completely!
    - Save this query as qrySSN.
    - Create a new query in design view. Add the 10 tables, and qrySSN as well.
    - Join each of the tables to qrySSN on the SSN field.
    - Double click each of the join lines, and select the option to return ALL records from qrySSN and only related records from the table.
    - Add LastName, FirstName, and SSN from qrySSN to the query grid.
    - Add the yyyyHours field from each of the tables to the query grid.

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge Tables (2003)

    Hans,

    That worked great. I was trying to do the same thing but I was including the hours in the first query and that seemed to throw things off. Its working now though.

    Thom

Posting Permissions

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