Too many fields defined

I've a weird issue with MS access 2007. If I run the following query:

SELECT QUERY_LNK_ORDERS_ITEMS.*, QUERY_LNK_ERASALES_ERACOND.*
FROM QUERY_LNK_ORDERS_ITEMS
INNER JOIN QUERY_LNK_ERASALES_ERACOND
ON QUERY_LNK_ORDERS_ITEMS.concat = QUERY_LNK_ERASALES_ERACOND.concat

It works, no troubles, then I want to include missing records with left/right join so I run:

SELECT QUERY_LNK_ORDERS_ITEMS.*, QUERY_LNK_ERASALES_ERACOND.*
FROM QUERY_LNK_ORDERS_ITEMS
INNER JOIN QUERY_LNK_ERASALES_ERACOND
ON QUERY_LNK_ORDERS_ITEMS.concat = QUERY_LNK_ERASALES_ERACOND.concat
UNION ALL
SELECT QUERY_LNK_ORDERS_ITEMS.*, QUERY_LNK_ERASALES_ERACOND.*
FROM QUERY_LNK_ORDERS_ITEMS
LEFT JOIN QUERY_LNK_ERASALES_ERACOND
ON QUERY_LNK_ORDERS_ITEMS.concat = QUERY_LNK_ERASALES_ERACOND.concat
WHERE QUERY_LNK_ORDERS_ITEMS.concat IS NULL
UNION ALL
SELECT QUERY_LNK_ORDERS_ITEMS.*, QUERY_LNK_ERASALES_ERACOND.*
FROM QUERY_LNK_ORDERS_ITEMS
RIGHT JOIN QUERY_LNK_ERASALES_ERACOND
ON QUERY_LNK_ORDERS_开发者_如何转开发ITEMS.concat = QUERY_LNK_ERASALES_ERACOND.concat
WHERE QUERY_LNK_ERASALES_ERACOND.concat IS NULL

And I get: too many fields defined

I don't understand... I'm selecting the exact amount of fields, where is this error coming from guys?

Thanks in advance Cheers

Maybe the problem has nothing to do with your JOINS, but with your UNIONS!

I Googled your error message and found this: http://www.mvps.org/access/tables/tbl0002.htm. Given that you have so many fields... Maybe that's the problem? Note that the various articles talk about MS Access's INTERNAL field count. Maybe the various UNION ALL clauses are limited by that internal field count

I don't know if this helps, but I don't think you would get any results from a left-join query that requires the left join column to be null, and the same for a right-join query where the right join column is null, which seems to be what's going on here. Maybe try switching the left and right joins?

Take a look at this article. It explains pretty well all the different joins.

In your case why don't you use a full outer join ?

SELECT QUERY_LNK_ORDERS_ITEMS.*, QUERY_LNK_ERASALES_ERACOND.*
FROM QUERY_LNK_ORDERS_ITEMS
FULL OUTER JOIN QUERY_LNK_ERASALES_ERACOND
ON QUERY_LNK_ORDERS_ITEMS.concat = QUERY_LNK_ERASALES_ERACOND.concat

Since you've found that the problem is with the UNIONs (comment on Spiny Norman's answer), I think your only optionis going to be a temporary table (though you might want to define its structure premanently, then insure that valid uses always clear it before repopulating). Then run each of your queries as an insert (maybe with the first as a make-table if you don't go with permanent structure), and select from that for your final result.

Did you get an answer to this one other way might lie in separating these and combining in a recordset.

Set rs = db.OpenRecordset(SQL1)
Set rs1 = db.OpenRecordset(SQL2)
Set rs2 = db.OpenRecordset(SQL3)
 .......