How to use UNION ALL and SORT BY

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

.

Relevant Pages

  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... tab4.login_date from (select distinct emailAddress from Users union ... I created the UserPrecedence table that describes the ordered ... ORDER BY CASE @sort ... The sp_ prefix is reserved for system procedures, and SQL Server first ...
    (comp.databases.ms-sqlserver)
  • Re: Increase field size
    ... UNION, GROUP, SORT, etc but I would want to limit the length, restrict ... the contents (allow Unicode and you will get Unicode ), ... Ah yes, table-level CHECK constraints, the DECIMAL data type (including ...
    (microsoft.public.access.formscoding)
  • RE: Query sort not working after NULL column
    ... There is no sorting in any of the queries except the last one. ... that and sort when the query is run from VB. ... UNION ALL but I'm sure that will complicate things even more and lead me to ... have to insert even another extra query to get rid of the duplicates. ...
    (microsoft.public.access.queries)
  • Re: Sorting problem
    ... It works but its sorting data like this: ... You add an order by clause to the last query, using the field names in the ... So if you want to sort by Data, ... UNION ALL ...
    (microsoft.public.access.queries)
  • Union Select sorting
    ... hidden column if you wish to sort by it. ... >I'm having trouble sorting a Union Select query. ... like to sort on a hidden field, but from what I've read in ...
    (microsoft.public.access.queries)