Too many fields using select query

[lorenzdominic_]

Hi

I have a problem where I have a query that produces 254 fields ( I
don't want to make it smaller, either access can handle 255 or it
cannot! ) It would appear that access cannot handle my query as I get
the lovely message 'Too Many Fields'

I run the query by using the Sum function on all the fields except I
use Group By on three of them.

Therefore 251 fields are summed and 3 are not.

However why do I get the Too Many Fields error.

I have compacted the database many times.

Deleted and restored relationships.

Started from scratch and remade the query.

Still the problem exists.

Does anyone know how to fix this?

Does anyone know how to dig into the core of access and reset something
that needs resetting? Such as a field count on a table or whatever?

This is a Microsoft Access issue not a user issue.

Regards
Dominic

 

[Arvin Meyer [MVP]]

Actually, I do not know if anyone has ever used the upper limits of the
specifications before because it is unlikely that a normalized database
would ever need that many. You might try splitting the query into multiple
smaller ones, then joining them together on a key and doing something like:

SELECT Query1.*, Query2.*
FROM Query1 INNER JOIN Query2 ON Query1.Key = Query2.Key;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[lorenzdominic_]

Hi Joseph

Thank you for your reply.

I am happy to change the design.

When I have time I will get back to you on this.

Regards
Dominic

 

[lorenzdominic_]

Hello Arvin

Thank you for you reply.

I am currently using the upper limits of Microsoft Access 2003 and it
cannot handle having 254 fields let alone 255 using the Group by and
Sum features of the Query builder.

'Too many fields'

But there are not.

Sometimes but not often it is necessary to De-normalise a database to
produce results in a faster time than if you normalised a database.

I am looking for an answer why Access cannot handle summing 254 fields
in a simple select query that does exactly what you stated in your
example sql statement.

Thanks.

Dominic