Union Query

[Keke Lee]

I have 2 queries to return below results:

resultABC:[codeABC], [qtyABC]
resultDEF:[codeDEF], [qtyDEF]

And I want to create a new query to show above results in below columns:

, [qtyABC], [qtyDEF]

whereAs [code] is a Union Select query to combine [codeABC] & [codeDEF] and
to take out the duplicates.

I am only able to create a SQL to query their combined results like:

SELECT [codeABC] AS [code], [qtyABC] AS [qty]
FROM [resultABC]
UNION SELECT [codeDEF] AS [code], [qtyDEF] AS [qty]
FROM [resultDEF]

whereas it returns combined value of [code] & [qty].

How can I rewrite the query that I can show the results
([code],[qtyABC],[qtyDEF]) I want?

Thanks

Keke

 

[Klatuu]

If both of your queries are based on the same table, there is another way:
SELECT

, Sum(IIf([code] = "ABC",[qty],0)) AS qtyABC, Sum(IIf([code] =
"ABC",[qty],0)) as qtyDEF FROM MyTable GROUP BY [code];

 

[Keke Lee]

No, my Union Query is to return values from other 2 query tables

If both of your queries are based on the same table, there is another way:
SELECT

, Sum(IIf([code] = "ABC",[qty],0)) AS qtyABC, Sum(IIf([code] =
"ABC",[qty],0)) as qtyDEF FROM MyTable GROUP BY [code];

--
Dave Hargis, Microsoft Access MVP


[QUOTE="Keke Lee"]
I have 2 queries to return below results:

resultABC:[codeABC], [qtyABC]
resultDEF:[codeDEF], [qtyDEF]

And I want to create a new query to show above results in below columns:

[code], [qtyABC], [qtyDEF]

whereAs [code] is a Union Select query to combine [codeABC] & [codeDEF] and
to take out the duplicates.

I am only able to create a SQL to query their combined results like:

SELECT [codeABC] AS [code], [qtyABC] AS [qty]
FROM [resultABC]
UNION SELECT [codeDEF] AS [code], [qtyDEF] AS [qty]
FROM [resultDEF]

whereas it returns combined value of [code] & [qty].

How can I rewrite the query that I can show the results
([code],[qtyABC],[qtyDEF]) I want?

Thanks

Keke
[/QUOTE][/QUOTE]

 

[Klatuu]

SELECT codeABC, qtYABC, 0 AS qtyDEF FROM resultABC UNION SELECT codeDEF, 0
As qtyABC, qtyDEF FROM resultDEF;
--
Dave Hargis, Microsoft Access MVP

No, my Union Query is to return values from other 2 query tables

If both of your queries are based on the same table, there is another way:
SELECT

, Sum(IIf([code] = "ABC",[qty],0)) AS qtyABC, Sum(IIf([code] =
"ABC",[qty],0)) as qtyDEF FROM MyTable GROUP BY [code];

--
Dave Hargis, Microsoft Access MVP


[QUOTE="Keke Lee"]
I have 2 queries to return below results:

resultABC:[codeABC], [qtyABC]
resultDEF:[codeDEF], [qtyDEF]

And I want to create a new query to show above results in below columns:

[code], [qtyABC], [qtyDEF]

whereAs [code] is a Union Select query to combine [codeABC] & [codeDEF] and
to take out the duplicates.

I am only able to create a SQL to query their combined results like:

SELECT [codeABC] AS [code], [qtyABC] AS [qty]
FROM [resultABC]
UNION SELECT [codeDEF] AS [code], [qtyDEF] AS [qty]
FROM [resultDEF]

whereas it returns combined value of [code] & [qty].

How can I rewrite the query that I can show the results
([code],[qtyABC],[qtyDEF]) I want?

Thanks

Keke
[/QUOTE][/QUOTE][/QUOTE]

 

[Keke Lee]

Thanks for the script.
I tried to query by this script, and it has retured 2 rows for a same code
if both qtyABC & qtyDEF <> 0.
I was inspired by some other posts here and have made used the 'left join' &
'right join' query to get my results.
Many thanks of your work anyway
Cheers
Keke

SELECT codeABC, qtYABC, 0 AS qtyDEF FROM resultABC UNION SELECT codeDEF, 0
As qtyABC, qtyDEF FROM resultDEF;
--
Dave Hargis, Microsoft Access MVP

No, my Union Query is to return values from other 2 query tables

If both of your queries are based on the same table, there is another way:
SELECT

, Sum(IIf([code] = "ABC",[qty],0)) AS qtyABC, Sum(IIf([code] =
"ABC",[qty],0)) as qtyDEF FROM MyTable GROUP BY [code];

--
Dave Hargis, Microsoft Access MVP


:

I have 2 queries to return below results:

resultABC:[codeABC], [qtyABC]
resultDEF:[codeDEF], [qtyDEF]

And I want to create a new query to show above results in below columns:

[code], [qtyABC], [qtyDEF]

whereAs [code] is a Union Select query to combine [codeABC] & [codeDEF] and
to take out the duplicates.

I am only able to create a SQL to query their combined results like:

SELECT [codeABC] AS [code], [qtyABC] AS [qty]
FROM [resultABC]
UNION SELECT [codeDEF] AS [code], [qtyDEF] AS [qty]
FROM [resultDEF]

whereas it returns combined value of [code] & [qty].

How can I rewrite the query that I can show the results
([code],[qtyABC],[qtyDEF]) I want?

Thanks

Keke
[/QUOTE][/QUOTE][/QUOTE]