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
LEFT JOIN Query5 ON Query6.qcrgacode = Query5.rgacode;
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.