Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Union Query and table field format question

    Hello, I am trying to use a query to display all the records from two tables.

    Both tables have the fields -RefNumber, FirstName, Surname

    The RefNumber field in each table has a specific format set for each table -
    Table1 = "NWC"000000
    Table2 = "CL"000000

    So each RefNumber field displays in each table as say NWC000035 or CL000012 and so on.

    I have tried a Union query to combine both tables, and while this works and shows the correct amount of fields, it does not display either "NWC000" or "CL000) at the start of the RefNumber table, it only shows say 35, or 12.

    How do I get the format prefix to display in the union query?

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You will need code similar to this using the format function

    SELECT "NWC" & Format([refnumber],"000000") AS ref_number, table1.results
    FROM table1;
    union SELECT "CL" & Format([refnumber],"000000") AS ref_number, table2.results
    FROM table2;
    In the first select NWC is added to the refnumber to give NWC1, the format pads the number with zeros to six digits.
    The second select is the same as the first but CL is used in lieu NWC

  3. #3
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Look at "DemoUnionQueryA2002.mdb" (attachment, zip).
    Look at Query1 (union query). Run this query.
    Another way.
    Run "Query2MakeTable", then run "Query3Append", look at "NewTable".
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    MStef-ZG
    I could not open your zip file.

  5. #5
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Hello Poppa Smurf!
    I don't know why you can not to open it.
    Try with can opener.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can confirm I can open it just fine.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    Thank you so much Poppa Smurf and MStef-ZG for supplying the code and example, it is perfect

Posting Permissions

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