Too many fields defined

0
votes

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

5
sqlms-accessms-access-2007join
try using an explicit column list... - Mitch Wheat
Hi Mitch, the thing is that I've a very very long list lol In fact, if I try with only one or two fields it works, but not if I include everything - CoolStraw
with no disrespect to either MS or yourself, Access is not a full-featured database product. It is intended for simple uses by non-technical users and does this well - but too often such products get used for things they're not designed for. If you're trying to do simulate a full join (as described below) you are clearly technically sophisticated, and you deserve to use a better database product. I strongly suggest that you try another database that can do this job better and easier. - Bob Jarvis - Reinstate Monica
Hi Bob, no offense at all, what you're saying is right and straight forward. The only thing you don't know is that MS access is a business requirement in my project, so I can't use another database, I've to stick to it and well... to make it work somehow :). Thank you for your highlight though ;) - CoolStraw
@Bob Jarvis: while Access can indeed be used by novice users, it's also a powerful development platform that developers who aren't novices can do a lot with. Jet/ACE is the database engine, and it does have its limitations (and its own SQL dialect), but those are not really relevant here. The data schema is a bad design, seems to me. - David-W-Fenton

5 Answers

2
votes

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

0
votes

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?

0
votes

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
0
votes

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.

0
votes

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)
 .......