RUNTIME ERROR 3190 - too many fields defined...??

[WebDude]

Dear Access2000 group,


My Table_Volunteers has 70 fields.

My query_A displays all 70 fields (from table_Volunteers) plus
an additional 17 calculated fields for a total of 77 fields.
And it works fine

My query_B displays all 70 Fields (from Table_Volunteers) plus
an additional 17 calculated fields for a total of 77 fields.
And it works fine.

My query_C displays all 70 Fields (from Table_Volunteers) plus
an additional 17 calculated fields for a total of 77 fields.
And it also works fine.


However, when i try to do the following......


SELECT * FROM query_A
UNION ALL SELECT * FROM query_B
UNION ALL SELECT * FROM query_C


i get this error......


"RUNTIME ERROR '3190' - too many fields defined"



Any ideas?
Cheeers,
WebDUDE out.

 

[Allen Browne]

70 fields + 17 calculated fields = 87 fields.
87 fields * 3 tables = 261 fields.
Access will probably max out at 255.

As a workaround, you could create another table with 87 fields.
Empty it with a delete query.
Populate it with 3 Append queries.

The design you describe sounds more like a spreadsheet than a relational
database. The long-term solution will probably be to read up on
"normalization", and create some related tables that have many *records*
instead of having many (repeating?) fields on the one table. That will also
avoid the whole problem of the UNION query, since the records will already
be multiple records instead of trying to combine the 3 sets of repeating
fields into one long list.

Here's a list of articles that may help you get started with relational
design:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[Duane Hookom]

Have you considered normalizing your tables? They sound very un-normalized
and you have hit a limit specification in Access.

 

[David W. Fenton]

70 fields + 17 calculated fields = 87 fields.
87 fields * 3 tables = 261 fields.
Access will probably max out at 255.

Why would that matter for a UNION query? I see that the numbers add
up, but don't understand the logic. Shouldn't the maximum width be
all that matters? Of course, if the queries don't have the same
number of columns, you can't UNION SELECT * on them, so maybe the
calculations needed to resolve the variable number of columns is
what is leading to the particular error message?

 

[WebDude]

Hi

As always, thank you guys for taking the time to reply. I spent the
last 5 days in the hospital, which is why im late with this. At any
rate, i have a very loose idea on how UNION queries work in MS Access
and my table could be normalized better.. so, ill review your posts
and do some more deep thinking.


Thanks,
WebDude.