I have a query of this structure:

Top Select Statement
UNION ALL
Bottom Select Statement;

My top select statement works fine if I run it by itself.
My bottom select statement works fine if I run it by itself.

Both selects return 16 columns and the corresponding columns are of the same data type.

I only get this problem when the top and bottom quereies are written in the new "from x inner join y on x.a=y.b" style. If I rewrite the query using the old fashinoned "From x,y where x.a = y.b" style then it works OK.

The error message I get is "Join expression not supported".

Any suggestion on how I might proceed would be appreciated.

Unfortunately Access (the Jet Engine more precisely) is slightly limited when it comes to evaluate complex SQL Join expressions. You could try to create 2 queries (Top Select and Bottom Select) then Union them with a third query, but if it works with a Where clause I would not bother to change anything.

I have written a utility program in Access using VBA which is used to compare different ways of writing a query to achieve the same results. My selected query times the amount of time a specific query takes. I am timing:
1) Join Logic versus Where Logic (new style vs. old style)
2) using Aliaes versus just using columns names
3) using subqueries versus not using them
4) Using views or not
5) Using stacked queries
6) Use of indexes

In other words, I am not interested in some specific query, nor am I particularly interested in the results or the data. I am building a tool which will help application programs optimize their queries - naturally the ones that take a very long time to run.

The Join Logic is very important and I must figure out a way of solving this problem. I will soon be trying a later version of Access, but Access 2000 is what I have right now.

Can you post a sample db with the tables and query? I've got Access 2000 on this PC and I've got union queries with select statements with joins like that. Are you specifying the fields or doing SELECT *?

I don't want to sound like a wet blanket but the Jet Engine is not better in Access 2003 or 2007 than it was in Access 2000.

I don't have tested the 2010 version extensively yet, but I dont have any reason to believe it was significantly improved in this last vertion. Bugs (or "singularities") that were present in the way Jet interprets certain SQL expressions (namely when handling Null values) in Access 2.0 are still there in Access 2010, and this is just an example.

It does not mean that Access isn't a great product, simply that it has its limitations and one needs to live with them.

Hi pbaldy,

I will eventually send a sample db with the tables and query, but I have to sanitize the data first, since it contains some propritary information.

I also just bought the book "Access 2000 Developer's Handbook" which I should receive in a few days. I hope it has some answers for me.

I am specifying the fields. There are 16 fields specified with aliases. The query involves 9 tables all joined together.

The thing interesting to me is that each query of the 2 involved works fine by itself. It is only when I do a UNION ALL that I get the error message.

BigCarole