Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unanswered: UNION query works until i close it and reopen

    I am trying to concatenate my 3 main tables using a UNION query. The 3 main tables are NVD_TABLE, RADIO_TABLE, and BFT_TABLE. I have figured out how to get the Admin and Serial fields to work, but the Location field is giving me trouble. I want the location field to lookup data from the LOCATION.Location table. I thought I had it figured out with the following sql code, it will work and I save the query and close it. As soon as I re-open it I get numbers in the location field instead of the locations. Can anyone tell me what i am doing wrong. Once this works I plan to add other lookup fields to the query.


    SELECT NVD_TABLE.Admin, NVD_TABLE.Serial, NVD_TABLE.Location
    FROM LOCATION INNER JOIN NVD_TABLE ON LOCATION.ID = NVD_TABLE.Location
    WHERE (((NVD_TABLE.[Admin]) LIKE "*" & [Admin Number] & "*") AND ((NVD_TABLE.[Serial]) LIKE "*" & [Serial Number] & "*"))

    UNION ALL

    SELECT RADIO_TABLE.Admin, RADIO_TABLE.Serial, RADIO_TABLE.Location
    FROM LOCATION INNER JOIN RADIO_TABLE ON LOCATION.ID = RADIO_TABLE.Location
    WHERE ((( RADIO_TABLE.[Admin]) LIKE "*" & [Admin Number] & "*") AND (( RADIO_TABLE.Serial) LIKE "*" & [Serial Number] & "*"))

    UNION ALL

    SELECT BFT_TABLE.Admin, BFT_TABLE.Serial, BFT_TABLE.Location
    FROM LOCATION INNER JOIN BFT_TABLE ON LOCATION.ID = BFT_TABLE.Location
    WHERE ((( BFT_TABLE.[Admin]) LIKE "*" & [Admin Number] & "*") AND (( BFT_TABLE.Serial) LIKE "*" & [Serial Number] & "*"));


    The result of a search for Q66 is this:

    Admin Serial Location
    Q66VB 326I Cage
    Q66C1B 069230 Vault
    Q66C2B 082529 Vault
    Q66C1 075733 Vault
    Q66C2 075748 Vault
    Q66C3 40189 Vault
    Q66BFB V4PU141000 Connex
    Q66BFT V4PU140953 Connex
    Q66BFR V4PU131674 Connex

  2. #2
    Join Date
    Aug 2011
    Posts
    5

    Added info

    Sorry, that was my first thread, I forgot the example of what it does on reopening.

    I ran this and it worked great, until I close the database and reopen it. I didn’t change any of the code either; the damn thing puts the numbers instead of the correct dropdown locations.

    Admin Serial Location
    Q66VB 326I 1
    Q66C1B 069230 3
    Q66C2B 082529 3
    Q66C1 075733 3
    Q66C2 075748 3
    Q66C3 40189 3
    Q66BFB V4PU141000 4
    Q66BFT V4PU140953 4
    Q66BFR V4PU131674 4

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Where are those numbers coming from..? Do you know where abouts in your table you have that data stored? The numbers it is giving you in error that is.
    Looking for the perfect beer...

  4. #4
    Join Date
    Aug 2011
    Location
    Richmond, VA
    Posts
    38
    I think you need to change this line

    SELECT NVD_TABLE.Admin, NVD_TABLE.Serial, NVD_TABLE.Location

    to this.

    SELECT NVD_TABLE.Admin, NVD_TABLE.Serial, LOCATION.Location

    in each SELECT statement. I think your Lookup fields are causing the problem. While they display the text field, they actually only store the the Location ID.


    Good Luck.

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Ooohh yeah... Seems obvious now I look at it again.

    Paxham's answer should sort it out for you.
    Looking for the perfect beer...

  6. #6
    Join Date
    Aug 2011
    Posts
    5

    location works but lookups don't now

    ok, I changed each of the three main tables in the code to LOCATION.Location and now the query consistantly shows me the words instead of the numbers, however, I don't have my lookups for the Location field. I need the field to lookup off of LOCATION.Location so I can make changes using the dropdown for data integrity.

    SELECT NVD_TABLE.Admin, NVD_TABLE.Serial, LOCATION.Location
    FROM LOCATION INNER JOIN NVD_TABLE ON LOCATION.ID = NVD_TABLE.Location
    WHERE (((NVD_TABLE.[Admin]) LIKE "*" & [Admin Number] & "*") AND ((NVD_TABLE.[Serial]) LIKE "*" & [Serial Number] & "*"))

    UNION ALL

    SELECT RADIO_TABLE.Admin, RADIO_TABLE.Serial, LOCATION.Location
    FROM LOCATION INNER JOIN RADIO_TABLE ON LOCATION.ID = RADIO_TABLE.Location
    WHERE ((( RADIO_TABLE.[Admin]) LIKE "*" & [Admin Number] & "*") AND (( RADIO_TABLE.Serial) LIKE "*" & [Serial Number] & "*"))

    UNION ALL

    SELECT BFT_TABLE.Admin, BFT_TABLE.Serial, LOCATION.Location
    FROM LOCATION INNER JOIN BFT_TABLE ON LOCATION.ID = BFT_TABLE.Location
    WHERE ((( BFT_TABLE.[Admin]) LIKE "*" & [Admin Number] & "*") AND (( BFT_TABLE.Serial) LIKE "*" & [Serial Number] & "*"))
    ORDER BY [Serial];

    RESULTS THIS BUT NO LOOKUPS IN THE LOCATION FIELD.

    Query1
    Admin Serial Location
    Q66C1B 069230 Vault
    Q66C1 075733 Vault
    Q66C2 075748 Vault
    Q66C2B 082529 Vault
    Q66VB 326I Cage
    Q66C3 40189 Vault
    Q66BFR V4PU131674 Connex
    Q66BFT V4PU140953 Connex
    Q66BFB V4PU141000 Connex

  7. #7
    Join Date
    Aug 2011
    Posts
    5

    how do i get the table to show right in here?

    it will make more sense if my cut and pasted example table would show correctly in here. I am new and need to learn how to post my questions better in here.

Posting Permissions

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