Too many fields in Access 2000 query

  • Thread starter Thread starter Anton
  • Start date Start date
A

Anton

Hi experts,

I have to design a select query with 240 fields in Access 2000. I don't
have other options in this case.

I always got "Too many fields" error. I re-created the whole queries
again, until finally I created 2 queries, the first one is 81 fields,
the second one is 162 fields, total is 243 fields, plus the join field
is 245, less than 255. Same error still happens.

I decided to make a table from the first query, and then create a
select query based on that table and the second query. The same error
still occurs.

Is there any way to solve it? Thank you very much..

Anton
 
Yeah, I hate it, but I am forced to do it, someone in the high places
will use the data in Excel :-( No choice.

The funny thing is: the same query previously was developed in Access
1997 which were then converted to Access 2000. And it works perfecty
fine now. But the process to generate the old query is so complicated,
requires manual input, I am trying to automate it. I checked using ADO
that the old query has 243 fields, that comes from 2 tables and 3
queries, and total fields in those tables and queries are 258! While my
newly designed query comes from one table and one query with total
fields of 245, but it didn't work at all!

Anton
 
Anton said:
Yeah, I hate it, but I am forced to do it, someone in the high places
will use the data in Excel :-( No choice.
Click to expand...

I can think of an alternative, if it makes sense to you: partition the
Excel table vertically (maybe into thirds) and populate it in steps.
You could fill 1/3 of the Excel table with each of 3 Queries, and the
final result will look just as good as if you'd done it in one step.
You might be able to put the 3 Queries into a Macro to simplify running
them in the proper order. Or you could have the Excel cells reference
the proper parts of more than one Access Query. My guess is that the
S.I.H.P. doesn't care about the internal structure of either the Excel
spreadsheet or the Access Tables, but rather that the results look good.

(BTW, Excel has a similar limit -- no more than 256 columns in a row.)
The funny thing is: the same query previously was developed in Access
1997 which were then converted to Access 2000. And it works perfecty
fine now. But the process to generate the old query is so complicated,
requires manual input, I am trying to automate it. I checked using ADO
that the old query has 243 fields, that comes from 2 tables and 3
queries, and total fields in those tables and queries are 258! While my
newly designed query comes from one table and one query with total
fields of 245, but it didn't work at all!

Anton
Click to expand...

You might check out the topic "Microsoft Access database query
specifications" in Access Help, but it won't be very comforting if you
find that you really can't use as many fields as you need. But if you
really do want to know what the maximum is, you could (in a junk COPY of
your database, not the working version) try reducing the number of
fields little by little until you have a set that works. Or you could
do a binary search: Does it work with 128 fields? Does it work with 128
+ 64 = 192 fields? (Etc.) But even if you find the exact answer, it
probably won't make you much happier.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Anton,

I am not sure whether these comments are relevant. I recently had a
table where Access wouldn't let me add any new fields, even though the
number of fields already in the table was less than 250. Compacting the
database did not resolve this. But I created a new .mdb and imported
all objects from the existing one, and after that the problem disappeared.
 
Vincent,

Thanks for the idea, I guess I have to split the query into two or
three different parts.

Regarding the 2nd issue, I have tested before that the query can run if
I remove two fields. May be another way is to negotiate with the guy
which fields he rarely used.

Thank you very much for your time and effort to help me solving this
problem.

Regards,

Anton
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query with too many fields 7
Make Table Query - Too Many Fields 6
Query field size quandry 2
Too many fields defined in select query 4
too many fields defined 2
Union Query - Too Many Fields 3
Split data in field on character return 0
export crosstab query too many fields defined 4

Back
Top