Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Arrow Unanswered: Union Query To Compare 3 Inventories

    I would like to create a query that compares 3 different inventory counts of our warehouse. I have created 3 separate tables and have put the serial numbers counted in each table. I would like to have 3 columns, each representing one tables data. The like serial numbers would line up across each column. If all 3 tables had the same serial, all 3 columns would have the serial number listed across the line. If only 2 tables had the same serial number, 2 columns would show the serial number and the third would be blank.

    I believe this will have to be a union query but I do not know how to create a union query. As you can probably tell, I am a newbie. I have attached a 2002 database with some simple tables. Any help would be appreciated !!

    Thanks,
    B&R
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2004
    Posts
    67
    Probably not a union query...

    A union query would combine the 3 fields (one from each table) into 1 field.

    It sounds like you want to do somethng else.
    What is the purpose of the query, or what will you do with it?

  3. #3
    Join Date
    Jul 2004
    Posts
    67
    Additional from my previous post:

    If each table with also have an "inventory count" field associated with each serial number, then you have some options as to how to show the inventory for each item for each warehouse. Is this how it will work?

  4. #4
    Join Date
    Feb 2004
    Posts
    139
    DavidMound

    Thanks for replying. The tables will be for 3 different inventories of the same warehouse. There will not be any quantity fields just the serial number recorded. We want to compare the inventories to see the variances in the inventory counts. For example:

    Inv 1 Inv 2 Inv 3

    12345 12345 12345
    8790 8790
    77112 77112

    This will show us which inventory has the discrepancy.

    I hope this helps explain what I am looking for!

    Thanks,
    B&R

  5. #5
    Join Date
    Jul 2004
    Posts
    67
    I'm sure there are a few ways to do this, but I'll leave it to someone else.

  6. #6
    Join Date
    Jul 2004
    Posts
    125
    Each query must have the same number of fields and data type and in the same order. Create a Select query for each of the tables in question and in the same order. Where the number of columns don't match, create a false column as an alias like ItemID: Null, to pad and match the number of columns. In union queries the order of display follows the first query, so use the query with the required order as the first in the union (ORDER BY). Omit ordering the other queries because they have no effect. Create a 4th (empty) query and paste each query's SQL string in the SQL view one after another, separated by a semicolon. The 2nd and 3rd queries require the word UNION to preceed the word SELECT.

  7. #7
    Join Date
    Jul 2004
    Posts
    125
    After I wrote the instructions, I noticed your sample DB. I created the queries with the Union as mentioned and uploaded it as INVreturned.
    Attached Files Attached Files

  8. #8
    Join Date
    Feb 2004
    Posts
    139
    Thanks for the HELP !!! Very much appreciated. You gave me the kickstart I needed to learn union queries. Keep helping people learn as it is so important to beginners like me.

    Thanks,
    B&R

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't see how this can be done with a union query. I would do it with subqueries. Something similar to:

    SELECT t1.Serial, (SELECT t2.Serial FROM t2 WHERE t2.Serial=t1.Serial), (SELECT t3.Serial FROM t3 WHERE t3.Serial=t1.Serial)
    FROM t1

    This is of course assuming that table 1 will have the serial numbers you want. If that's not the case, then you need to create another table containing ALL of the serial numbers you want to query and use that as your base. Well, in all truth, if you don't have that kind of table all-ready, then you have a schema issue.
    Last edited by Teddy; 08-12-04 at 09:53.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    I don't see how this can be done with a union query. I would do it with subqueries. Something similar to:

    SELECT t1.Serial, (SELECT t2.Serial FROM t2 WHERE t2.Serial=t1.Serial), (SELECT t3.Serial FROM t3 WHERE t3.Serial=t1.Serial)
    FROM t1

    This is of course assuming that table 1 will have the serial numbers you want. If that's not the case, then you need to create another table containing ALL of the serial numbers you want to query and use that as your base. Well, in all truth, if you don't have that kind of table all-ready, then you have a schema issue.
    The way I see it this is just a LEFT JOIN issue where you have to decide which table will always have a value and will be used as the pivot point for the left join ... Something like:

    SELECT T1.Serial, T2.Serial, T3.Serial FROM (T1 LEFT JOIN T2 ON T1.Serial=T2.Serial) LEFT JOIN T3 ON T1.Serial=T3.Serial;
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by M Owen
    The way I see it this is just a LEFT JOIN issue where you have to decide which table will always have a value and will be used as the pivot point for the left join ... Something like:

    SELECT T1.Serial, T2.Serial, T3.Serial FROM (T1 LEFT JOIN T2 ON T1.Serial=T2.Serial) LEFT JOIN T3 ON T1.Serial=T3.Serial;
    I like your idea better.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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