Too many fields defined

Question

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

Answer 1

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