Union?

[DAW]

I have a union query that is not combining correctly. i.e. it is returning all the values from the first part followed by the results of the second. I really wanted them combined on the same row where appropriate - any ideas?

SELECT Yr, Mth, CNo, Customer, AON, AOC,Null as CON,Null as COC FROM qry_Sub_AO;
UNION ALL 
SELECT Yr, Mth, CNo, Customer,Null as AON,Null as AOC,CON, COC FROM qry_Sub_CO;

I should add that the data in qry_Sub_AO and qry_Sub_CO comes from the same (third) query so the data types are obviously the same.

 

[winshent]

use a group by query ????

SELECT U.Yr, U.Mth, U.CNo, U.Customer, U.AON, U.AOC, U.CON, U.COC
FROM (SELECT Yr, Mth, CNo, Customer, AON, AOC,Null as CON,Null as COC FROM qry_Sub_AO
UNION ALL
SELECT Yr, Mth, CNo, Customer,Null as AON,Null as AOC,CON, COC FROM qry_Sub_CO) AS U GROUP BY U.Yr, U.Mth, U.CNo, U.Customer, U.AON, U.AOC, U.CON, U.COC

 

[MSAccessRookie]

I believe that the following query should solve your problem.

Select Yr, Mth, CNo, Customer, AON, AOC, CON, COC from
(
SELECT U.Yr, U.Mth, U.CNo, U.Customer, U.AON, U.AOC, U.CON, U.COC
FROM (SELECT Yr, Mth, CNo, Customer, AON, AOC,Null as CON,Null as COC FROM qry_Sub_AO
UNION ALL
SELECT Yr, Mth, CNo, Customer,Null as AON,Null as AOC,CON, COC FROM qry_Sub_CO) AS U GROUP BY U.Yr, U.Mth, U.CNo, U.Customer, U.AON, U.AOC, U.CON, U.COC
)
Order by Yr, Mth, CNo, Customer, AON, AOC, CON, COC

 

[neileg]

Eh? If you want data on the same row (ie as a single record) you won't get this from a union query. That's not what they do.

 

[MSAccessRookie]

Eh? If you want data on the same row (ie as a single record) you won't get this from a union query. That's not what they do.

Neileg is right The query provided will return all rows sorted.

Try this one instead (remember that you need to substitute Table and Column names as required). The IIf Statement is there to prevent duplication of the common rows that have different years. You may need to add one for the other common fields as well (Mth, Cno, and Customer) if there can be duplicates.

SELECT
IIf(Table9.Yr IS NULL, Table8.Yr, Table9.Yr) AS Yr, Table8.Mth, Table8.CNo, Table8.Customer, Table8.AON, Table8.AOC, Table9.CON, Table9.COC
FROM Table8 Left Join Table9 ON Table8.Customer=Table9.Customer
UNION
SELECT
IIf(Table9.Yr IS NULL, Table8.Yr, Table9.Yr), Table9.Mth, Table9.CNo, Table9.Customer, Table8.AON, Table8.AOC, Table9.CON, Table9.COC
FROM Table9 Left Join Table8 ON Table9.Customer=Table8.Customer
;