Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: Union Query...help!

    I created a union query that merges three tables. A SUPERVISOR table, an AUDITOR table , and a CUSTOMERSERVICEREP table. The union query creates a total employee table. I 'pulled' the same fields from each of the three tables; <employee#>, <Firstname>, and <LastName>. The <employee#> field is set as a autonumber for each table and each has a different format. ex. SUP000001...AUD000001... and CSR00001. But the union query does not pull these formats, all I get is the basic number which causes duplicate numbers. Does any one know why this happens and how I can get the autonumbers to 'pull' correctly?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "The <employee#> field is set as a autonumber for each table and each has a different format. ex. SUP000001...AUD000001... and CSR00001"

    how did you get it to do that? an autonumber field does not contain characters

    you can try

    select 'SUP'+employee, otherfields
    from supervisor
    union all
    select 'AUD'+employee, otherfields
    from auditor
    union all
    select 'CSR'+employee, otherfields
    from customerservicerep

    don't forget to use UNION ALL instead of UNION


    rudy
    http://r937.com/

  3. #3
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Ohhhh!, right. Good idea. Sometimes its easy to overlook the easiest idea. THANKS!

    I set the autonumber field FORMAT line in the table design to add the characters to each number. It works in the table. Everytime I add a new record to the tables it will add the appropriate prefix and 'step' the number. But maybe this explains why the union query wasn't working.

    THANKS!!!

Posting Permissions

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