Error: "Too many fields defined."

[lexbmo]

i can't seem to get past this one! i only have 6 fields in this particular query... it does draw on a large collection of other queries because 4 of the fields are user defined and call on a bunch of other previous queries.

how do i get around this? should i think about migrating to another dbase system?

thanks!
lex

 

[MichaelRed]

migration probably won't help. most relational dbs limit recordsets to 255 fields. most also "count" ALL of the fields required to derive the final soloution. Often, "things" like parameters and criteria are also counted as fields. a soloution would be to include some 'make table' queries and use the resulting recordsets instead of queries.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[lexbmo]

ok i understand. is it easy to create a 'make table'? will the table be created dynamically? or would i have to run the query each time?

i'm just delving into dbase so thanks for the help!
the most difficult thing for me is the mix of SQL and 'Access' within Access (i guess that's Jet).

lex

 

[tlbroadbent]

Quick note: SQL Server 7.0 allows up to 1024 columns per row.

Lex,

Could you post your query here? And perhaps a couple of the underlying queries?

Thanks, Terry

http://members.home.net/tlbroadbent/index.htm

 

[MichaelRed]

If you are " ... just starting ... " Ms. Access, then you are presumably using the query grid. If so, then "Make Table" is a selection from the query type on the tool bar. You will need to either run it manually of create a small procedure to execute it.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[lexbmo]

the queries are very simple. of the nature:
(SELECT [] FROM [] WHERE [].[] = "&quot)*(SELECT [] FROM [] WHERE [].[] = "&quot)*[]![]

BUT i used a series of those for each situation (raw cost of materials and then for total with markup cost) and so the final table pulls on about 4-6 previous queries.

is there a way to condense the queries into on? i would have done that to begin with but Access will only allow me 255 characters per field!

and i did see the 'Make Table' query type in Access... but as you said i'd need to run it each time.

thanks!
lex

 

[lexbmo]

the queries are very simple. of the nature:
(SELECT [] FROM [] WHERE [].[] = "&quot)*(SELECT [] FROM [] WHERE [].[] = "&quot)*[]![]

BUT i used a series of those for each situation (raw cost of materials and then for total with markup cost) and so the final table pulls on about 4-6 previous tables with multiple queries within.

is there a way to condense the queries into on? i would have done that to begin with but Access will only allow me 255 characters per field!

and i did see the 'Make Table' query type in Access... but as you said i'd need to run it each time.

thanks!
lex

 

[MichaelRed]

... but I also said you can do them from a module, just execute the whole set (except perhaps your last one) of queries ina single module. It looks like this is just a 'set -up' for a form/report. If it is fro a from, do the module/set of queries on load (perhaps activate ... ) if it is for a report do them from the form which opens the report, then open the report.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Cagliostro]

maybe in one select yu put many fields than in every other selects? John Fill

ivfmd@mail.md

 

[lexbmo]

ok, i'm on a mission now... have to figure out the Module side of Access. sounds like i may be able to dump all of the queries combined in the module. that would be a much more orderly solution than my plethora of query tables!

again, thanks!
lex