Too many fields using select query

  • Thread starter Thread starter lorenzdominic_
  • Start date Start date
L

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
 
Hi

I have a problem where I have a query that produces 254 fields ( I
don't want to make it smaller,
Click to expand...

While I guess their may be a good reason for needed that many fields, I
have not encountered one yet and I have encountered may that have large
numbers of fields do to poor table design.
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.
Click to expand...

I would guess it is an issue for both. You say you don't "want" to make
it smaller. That makes me wonder.

How would you feel about changing the design of your database and ending
up with a faster more functional result that has all the features you want
or need without having all those fields?? If that sounds interesting, why
not try providing some details about all those fields and their
relationships and give us a chance.
 
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
 
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
 
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
 
Back
Top