Join Expression not Supported in Access 2000

[BigNorm]

I have a top query which works fine, and a bottom query will also works fine when you run them by themselves, but when you combine them with a union, I get the message "Join Expression not supported". Both top and bottom queries return 15 columns and the data types of the 15 columns in each query match. Any ideas.

[NeoPa]

My first idea would be to express more clearly your actual situation. Posting SQL is often a good early step, but don't let this stop you from explaining your situation using clear terminology in the question. Trust me, the help you can receive is heavily dependent on how easy it is to understand what you want.

[BigNorm]

I have a query that is like:

Select w.a,x.b,y.c,z.d 
from w,x,y,z
union all
select e.f,g.h,i.j,k.l
from e,g,i,k;

I call the two queries my top query and my bottom query with a union all between them.

FACT 1: The top query by itself works fine.
The bottom query by itself works fine.

FACT 2: Both top and bottom query have the same number of columns and the corresponding columns are of the same data types.

When I combine them in a UNION ALL I get the error message "Join Expression not Supported".

The actual top query is about 30 lines, and the bottom query is about 20 lines. The top query joins 9 tables, and the bottom query joins 3 tables.

All I am looking for is a clue or suggestion of what I could try to solve this problem or debug this problem.

[NeoPa]

Dealing with such complex queries is always a bit fraught. Fundamentally I see no specific reasons for the message, but you had to simplify the SQL to post. I get that. Maybe you simplified the important information out though. Maybe Jet SQL simply cannot handle the number of separate record sources involved in the overall query.

Is it true that none of the tables are actually JOINed at all, but simply listed separated by commas? Frankly, I find it hard to envisage a query that vast and so randomly put together that makes any sense. I cannot comment directly, as I cannot see what you're working on, but I'd be very seriously suspicious that there is something very wrong with the design of your database if it includes a query such as you've described.

In short, I suspect there may be limits in Jet SQL that you've exceeded in your SQL, but your focus should be on finding a way to reorganise your project to avoid such a design - rather than to work out how to get beyond the limits. I hope that makes sense.

[BigNorm]

I just bought the book "Access 2000 Developer's Handbook" from Amazon. I should receive it sometime next week. It seems to be a good book, and hopefully will have some answers.

The query that I am trying to use is currently in use by many people, and I do not have the liberty to change the design of the database. It is a complex application, but the application does a host of different things.