Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: combining values using UNION statement

    What I like to do is to display all quarters for each code, if there is null value for that particular Code the value for that field would be 0.

    I have two queries
    Qry1 has Date , Code, Null fields. Qry2 has Date, qCode, Total fields.
    4Q 1998, 1, 0 4Q 1998, 3, 2
    4Q 1998, 2, 0 4Q 1998, 5, 1
    “, “, 0
    Query 1 contains all dates(in quarter), all codes (1-19), null values for code that doesn’t have a less than 1 in the total field.
    Query 2 contains an actual record.
    Dates that actually occurre, the code, and the total(value)for the code.


    I would like my result look like this
    Date rgaCode Total
    4Q 1998 1 0
    4Q 1998 2 0
    4Q 1998 3 2
    4Q 1998 4 0
    4Q 1998 5 1

    This is what is displaying
    Date rgacode qcrgacode null
    4Q 1998 1 1 0
    4Q 1998 2 2 0
    4Q 1998 3 3 0
    4Q 1998 3 3 2
    4Q 1998 4 4 0
    4Q 1998 5 5 0
    4Q 1998 5 5 1


    This is my query
    SELECT Query5.Date, Query5.rgacode, Query6.qcrgacode, Query5.null FROM Query5
    LEFT JOIN Query6 ON Query5.rgacode = Query6.qcrgacode
    UNION SELECT Query6.Date, Query5.rgacode, Query6.qcrgacode, Query6.total
    FROM Query6
    LEFT JOIN Query5 ON Query6.qcrgacode = Query5.rgacode;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you don't need the union

    the first query, slightly modified, will give you what you want

    SELECT Query5.Date, Query5.rgacode, Query6.total
    FROM Query5
    LEFT JOIN Query6
    ON Query5.rgacode = Query6.qcrgacode

    if you're interested, here's how i understood your situation --

    if Q5 has all the combinations, then doing a left join to Q6 will get all the Q5 rows, including those which match Q6 rows, but you are selecting "null" instead of the Q6 total

    then the second query of the union, Q6 left join to Q5 finds all the rows of Q6 no problem, but no unmatched Q5 rows, since there can't be any, since all the combinations are supposedly present in Q5

    now since the same Q6 row was selected twice, once with a null in the total column from Q5 and once with the real total from Q6, therefore that Q6 rgacode will appear twice in the results

    conclusion: modify the first query as shown above


    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ummm... SQL is not my hobby, but just for fun:

    "....This is my query
    SELECT
    Query5.Date,
    Query5.rgacode,
    Query6.qcrgacode,
    Query5.null
    FROM....."

    -- why Query6.qcrgacode, you dont want it returned
    -- why Query5.null, you dont want it returned
    -- and where is Total? this one you do want returned

    ...but like I say, SQL is not my thing: there might be some join/union hiccups too.

    izy

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    apologies r937.
    I must learn to type faster: you were not posted when I started.

    izy

  5. #5
    Join Date
    May 2002
    Posts
    395

    combining the results

    I need Codes (1-19) for each quarter and if that particular doesn’t have a total/value that will populate with a 0 value.
    1Q 1998 1 0
    1Q 1998 2 10
    1Q 1998 3 4

    I was trying to combine the two queries together so the final query will give me all the existing dates and rgacodes from qry1 and display the totals from all the rgacodes in qry2.
    if a rgacode doesn’t have a value(not in qry2) fill it with 0 value.

    Query 5 contains all dates(in quarter), all codes (1-19), zero values in the total field.
    Query 6 contains dates that actually occurred, the code and the total(value)for the code.

    The reason for a zero value in Qry5 is that some of the codes do not have a value for that quarter, and when I join the two queries together any rgacodes that do have a value in qry6, will populate with a 0 value . I thought Union is the right approach.

    What I need are all the dates and rgacode from qry5, and totals for qcrCodefrom qry6. If rgacode is in Qry5 and is not in Qry6 the total field will be 0 for that rgacode. So I need the
    Total field either populate with 0 or the value.

    Qry5 has Date, rgacode, Null. Qry6 has Date, qcrCode, total.
    4Q 1998, 1, 0 4Q 1998, 3, 2
    4Q 1998, 2, 0 4Q 1998, 5, 1
    “, “

    I would like my result display unique rgaCode and populate with either 0 or a value.
    It look something like this:
    Date rgaCode Total
    4Q 1998 1 0
    4Q 1998 2 0
    4Q 1998 3 2
    4Q 1998 4 0
    4Q 1998 5 1

    I tried the query you suggested, it repeat the same code for each total(?) and the total field doesn’t match qry6.
    eg:
    4Q 1998 1 2
    4Q 1998 1 8
    4Q 1998 1 9

    Thanks! I appreciate any help I can get.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT Query5.Date, Query5.rgacode
    , iif(isnull(Query6.total),0,Query6.total) as total
    FROM Query5
    LEFT JOIN Query6
    ON Query5.rgacode = Query6.qcrgacode

    if this is not right, there's something else funny happening inside those queries

Posting Permissions

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