Access ORDER BY / UNION question

[halberd4]

Hello. I have an Access2003 db that I need to create a 'combined' query for. This query will then become the source for a listbox. The query needs to combine Query1 (example)

SELECT Aa, Bb, Cc, Dd FROM Table1
WHERE Cc=3
ORDER BY Aa, Bb, Dd

with Query2 (example)

SELECT Aa, Bb, Cc, Dd FROM Table1
WHERE Cc<>3
ORDER BY Cc, Aa, Bb, Dd

and produce a combined output similar to this (minus header)

Aa    Bb    Cc    Dd
---------------------------
Bremmer    Amy    3    455
Bremmer    Bill    3    478
Connor    Carl    3    978
Durf    Dan    3    670
Ensign    Ed    3    406
Barnes    Fred    1    748
Bremmer    Bill    1    885
Finn    Fiona    1    054
Creedy    Cal    2    990
Enfield    Earl    2    031
Anon    Alfred    4    054
Dempsy    Don    4    990
Dempsy    Sue    4    031
Swartz    Sue    5    036
 

When run separately each query works and sorts successfully using ORDER BY, but when queries are combined with UNION or UNION ALL sorting breaks within each query. The ORDER BY won't work over the whole group because of the 1,2 formatting that must be maintained. I've tried several solutions posted to other sites for ORDER BY/UNION with no luck. Any help would be appreciated. Thanks.

[halberd4]

Should have been more specific about 'no luck';
Q1 by itself works...

SELECT Table1.Aa AS [ALabl], Table1.Bb AS [BLabl], Table1.Cc AS [CLabl], Table1.Dd AS [DLabl]
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc=3
ORDER BY Aa, Bb, Dd;

As does Q2...

SELECT Table1.Aa AS [ALabl], Table1.Bb AS [BLabl], Table1.Cc AS [CLabl], Table1.Dd AS [DLabl]
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc<>3
ORDER BY Cc, Aa, Bb, Dd;

But this attempt at a UNION returns the records in Q1,Q2 format, but faulty sorting withing Q groups...

SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM (SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc=3
ORDER BY Aa, Bb, Dd)
UNION ALL
(SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc<>3
ORDER BY Cc, Aa, Bb, Dd);

UNION without ALL returns records completely randomized...

SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM (SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc=3
ORDER BY Aa, Bb, Dd)
UNION
(SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc<>3
ORDER BY Cc, Aa, Bb, Dd);

This gives "syntax error in 'Dd UNION ALL SELECT Table1.Aa"...

INSERT INTO TmpTbl (Cc, Aa, Bb, Dd)
SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc=3
ORDER BY Aa, Bb, Dd
UNION ALL
SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc<>3
ORDER BY Cc, Aa, Bb, Dd;

This gives "syntax error in INSERT INTO statement"...

INSERT INTO TmpTbl (Cc, Aa, Bb, Dd)
(SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc=3
ORDER BY Aa, Bb, Dd)
UNION ALL
(SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc<>3
ORDER BY Cc, Aa, Bb, Dd);

And this returns records with no 1,2 grouping, and corrupts the query with '[%$##@_Alias]' codes...

INSERT INTO TmpTbl (Aa, Bb, Cc, Dd)
SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM (SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc=3
ORDER BY Aa, Bb, Dd
UNION ALL
SELECT Table1.Aa, Table1.Bb, Table1.Cc, Table1.Dd
FROM Table4 RIGHT JOIN (Table3 INNER JOIN (Table2 RIGHT JOIN Table1 ON Table2.Tbl2Index=Table1.Tbl2Curr) ON Table3.Tbl3Index=Table1.Tbl3Curr) ON Table4.Tbl4Index = Table1.Tbl4Curr
WHERE Cc<>3
ORDER BY Cc, Aa, Bb, Dd);

The newbie says 'thanks for your patience'.

[Rabbit]

Hello. I have an Access2003 db that I need to create a 'combined' query for. This query will then become the source for a listbox. The query needs to combine Query1 (example)

SELECT Aa, Bb, Cc, Dd FROM Table1
WHERE Cc=3
ORDER BY Aa, Bb, Dd

with Query2 (example)

SELECT Aa, Bb, Cc, Dd FROM Table1
WHERE Cc<>3
ORDER BY Cc, Aa, Bb, Dd

and produce a combined output similar to this (minus header)

Aa    Bb    Cc    Dd
---------------------------
Bremmer    Amy    3    455
Bremmer    Bill    3    478
Connor    Carl    3    978
Durf    Dan    3    670
Ensign    Ed    3    406
Barnes    Fred    1    748
Bremmer    Bill    1    885
Finn    Fiona    1    054
Creedy    Cal    2    990
Enfield    Earl    2    031
Anon    Alfred    4    054
Dempsy    Don    4    990
Dempsy    Sue    4    031
Swartz    Sue    5    036
 

When run separately each query works and sorts successfully using ORDER BY, but when queries are combined with UNION or UNION ALL sorting breaks within each query. The ORDER BY won't work over the whole group because of the 1,2 formatting that must be maintained. I've tried several solutions posted to other sites for ORDER BY/UNION with no luck. Any help would be appreciated. Thanks.

Umm.. you might not realize this but the combination of both these queries is simply:

SELECT Aa, Bb, Cc, Dd
FROM Table1
ORDER BY Cc, Aa, Bb, Dd

Query 1 returns all rows where Cc = 3, Query 2 returns all rows where Cc <> 3. So the combination of the queries returns all rows.

[halberd4]

Umm.. you might not realize this...Query 1 returns all rows where Cc = 3, Query 2 returns all rows where Cc <> 3. So the combination of the queries returns all rows.

Sorry, I should have been clearer: What I need is all rows where Cc = 3 (sorted on Aa, Bb, Dd), followed by all rows where Cc <> 3 (sorted on Cc, Aa, Bb, Dd), in the same singluar set of results, which can be pointed to as a control for a listbox (a query?).

[Rabbit]

Sorry, I should have been clearer: What I need is all rows where Cc = 3 (sorted on Aa, Bb, Dd), followed by all rows where Cc <> 3 (sorted on Cc, Aa, Bb, Dd), in the same singluar set of results, which can be pointed to as a control for a listbox (a query?).

I see, try this:

SELECT Aa, Bb, Cc, Dd
FROM Table1
ORDER BY IIf([Cc] = 3, 0, [Cc]);

[halberd4]

All hail Rabbit! What ended up working was a slight tweak on your solution:

SELECT Aa, Bb, Cc, Dd
FROM Table1
ORDER BY IIf([Cc] = 3, 0, [Cc]), Aa, Bb, Dd;

More practice needed in riding the VBA/SQL fence. Many thanks for both the expertise and the patience!

[Rabbit]

Not a problem, good luck.