I am embarrassed to post this, it seems so simple.

I have read the archives and the solutions do not seem to work with my code. My programing does not recognize the suggested commands.

I am using Access2K and FrontPage2K. Hey, don't laugh.

Here is the code:

SELECT Parking
FROM Bus
WHERE BusNumber=::BusNumber::
UNION ALL
SELECT Parking2
From Parking1

The second SELECT results need to ASC alphabetically.

Of course, GROUP BY applies itself to the whole query, which will not work for me.

I tried to apply the solutions I found in the archives but either FrontPage or Access will not recognize the syntax.

Thanks for any help.

Rosenhan

actually, it's the other way around

each subselect can have its own GROUP BY, but the overall UNION query can have only one ORDER BY

does that help your situation?


rudy
http://r937.com/

I appologize for wasting everyone's time. In my haste I have made the wrong post. THe post should have been:

UNION ALL ORDER BY

The problem is in the ORDER, Not GROUP

SELECT Parking
FROM Bus
WHERE Bus.BusNumber=::BusNumber::
UNION ALL
SELECT Parking2
From Parking1

In this query, ORDER BY can only be applied at the end, and will ORDER the entire query.

I only want to ORDER the second SELECT.

Thanks

Rosenhan

try this --

SELECT Parking
FROM Bus
WHERE Bus.BusNumber=::BusNumber::
UNION ALL
SELECT *
from ( select Parking2 From Parking1 order by parking2 )

or perhaps something like this

SELECT 1 as sortkey1
, ' ' as sortkey2
, Parking
FROM Bus
WHERE BusNumber=::BusNumber::
UNION ALL
SELECT 2 as sortkey1
, Parking2 as sortkey2
, Parking2
From Parking1
order by sortkey1, sortkey2


rudy

Rudy,

Your second example works correctly. The first, I knew from experience, would not work.

I tried previously to apply my own version of your number 2 example, but could not get the syntax accepted. Yours works fine.

Thank you very much.

Rosenhan