How to use UNION ALL and SORT BY

[tothlaci]

Hi,

I have an asp site where I sell some products. Product categories are
stored in different tables. There are different asp product pages,
which collects data from these tables.
I have also created an all-product-page, which works fine with the
following sql query:

SELECT * FROM kaldoi
WHERE (id LIKE 'KALDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkdoi
WHERE (id LIKE 'MKDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkfut
WHERE (id LIKE 'MKFUT%' AND soldout <> 1)

This page lists all my products by product category.
I'd like to sort the results by "productid", however when I insert
'SORT BY productid' at the end of the statement, I receive the
following error message from my editor (FP2003):

Server error: Unable to retrieve schema information from the query:

SELECT * FROM kaldoi
WHERE (id LIKE 'KALDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkdoi
WHERE (id LIKE 'MKDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkfut
WHERE (id LIKE 'MKFUT%' AND soldout <> 1)
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(id LIKE 'MKFUT%' AND soldout <> 1)
SORT BY productid'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
I couldn't solve it, though I have browsed through dozens of threads
here.
Any help will be appreciated

[Allen Browne]

It's:
ORDER BY productid

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<to******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi,

I have an asp site where I sell some products. Product categories are
stored in different tables. There are different asp product pages,
which collects data from these tables.
I have also created an all-product-page, which works fine with the
following sql query:

SELECT * FROM kaldoi
WHERE (id LIKE 'KALDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkdoi
WHERE (id LIKE 'MKDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkfut
WHERE (id LIKE 'MKFUT%' AND soldout <> 1)

This page lists all my products by product category.
I'd like to sort the results by "productid", however when I insert
'SORT BY productid' at the end of the statement, I receive the
following error message from my editor (FP2003):

Server error: Unable to retrieve schema information from the query:

SELECT * FROM kaldoi
WHERE (id LIKE 'KALDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkdoi
WHERE (id LIKE 'MKDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkfut
WHERE (id LIKE 'MKFUT%' AND soldout <> 1)
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(id LIKE 'MKFUT%' AND soldout <> 1)
SORT BY productid'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
I couldn't solve it, though I have browsed through dozens of threads
here.
Any help will be appreciated