Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Union Query? (AC97 SR-1)

    I have two tables which through 2 different data entry activities, record scrap by shift. Operation A records scrap for the 1st Production Process, and Operation B for the 2nd. There are 3 shifts per day for Operation A and two shifts per day for Operation B. The keys to both tables inlude Date & Shift.

    The Problem:
    I have created total queries for each Operation, which gives me total scrap per day for each operation.. I wish to add the data from these two queries together, to give a total scrap per day for the combined operation. Because on some days either Operation may be inactive, the dates in each table may not have a coresponding entry in the other. (Caused by one of the operations working weekends. or machine down time).

    I realise that I probably need a Union query, but 'Help' says that I need to create this directly using SQL, a subject that so far I have been able to avoid.

    Could some kind soul help?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Union Query? (AC97 SR-1)

    The UNION queries in SQL are very straight forward. They have the form:
    SELECT ScrapDate, ScrapShift, Sum(Weight) as SumofWeight FROM Table1
    UNION
    SELECT ScrapDate, ScrapShift, Sum(Weight) as SumofWeight FROM Table2

    You can use the design grid to design each query before going into SQL and doing a copy and paste it into a new query's SQL.

    What you must do is to ensure that the fields both SELECTs are the same data types.

    If you need any more help, please post back and some kind soul will help.

    Pat

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Union Query? (AC97 SR-1)

    I don't understand why you're using two tables to record the same kind of information. That makes it much harder to work with the data. With a single table that included fields for Operation and Shift, you wouldn't need a union query at all.

    However, the easiest way to create a union query is to create two separate select queries, one for each table. Then copy the SQL for each to the SQL view of a new query and glue them together using the UNION keyword. It would look something like this:

    SELECT * FROM TableA
    UNION
    SELECT * FROM TableB

    The thing to keep in mind is that the field in each SELECT query much be in the same order and have the same datatypes, so if the structure of the tables is slightly different, you'll need to specify the precise fields to use in each SELECT statement. Each SELECT query can also have a WHERE clause to filter the records for that query and the whole thing can have an ORDER BY at the end. Does that help?
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Query? (AC97 SR-1)

    Thanks Patt,

    It's soooo simple when someone shows you the way!!

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Query? (AC97 SR-1)

    Thanks Charlotte,
    Reason for two tables is the fact that there are two quite different operations. The fields (apart from scrap), are quite different for each operation, and the data is not compatible with the oposite table. The actual scrap product is the same from both operations, but where one operation extrudes the raw material and has trim as scrap, the second processes it into Finished product. This has excess material which is scrap.


    Have reviewed both solutions (yours and Patt's) and have seen the light.


    Thanks again

Posting Permissions

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