Access Query: Too many fields defined.

[Timothy Andrepont]

I am at the last phase of producing an extensive analysis report. The last calculation I need to the query QryCom_CalculationFinal gives me the "Too many fields defined" error. I have verified that "Ps_Score" calculation push the system into overload, as QryComp_CalculationFinalPrt01 without this calculation works fine.

I in ignorance, split into component queries, but this has not helped.

A work around solution would be greatly appreciated.

See attached PSAssm03

I look forward to any suggestion.

Timothy

 

[Mihail]

Can't open in A 2007

 

[Galaxiom]

Sorry to bear bad news but the problem is the way you have structured the data. One table has 141 fields and this should rings alarm bells. Despite appeances, databases tables are not spreadsheets.

You need to understand how to construct relational tables and normalize data. Until you reconstruct the data properly you will experience ever increasing problems.

 

[Pat Hartman]

One of the tables has many repeating groups and even repeating groups within repeating groups. You need to normalize the schema by separating these groups into their own tables. Your form will need to use subforms to display these many-side tables.

 

[Timothy Andrepont]

Table: Too many fields defined.- To Normalized Tables

All,

Thanks! Thank!

I am a beginner in most things Relational.

If there is a good place to get a step by step guide to the simplest way to convert existing table with all data into a normalized relational data system, please let me know.

I knew the way I had the data was not the best system but did not know a simple way to proceed to normalized system. Originally I had all data in 11 tables but could not figure out how to turn them into a normalized system. Since much data has been added to tblTotal since tables were consolidated, I will need to proceed from the present status.

Step one would be great, so I could begin working.

Help will be greatly appreciated.

Timothy
-

 

[SOS]

Re: Table: Too many fields defined.- To Normalized Tables

If there is a good place to get a step by step guide to the simplest way to convert existing table with all data into a normalized relational data system, please let me know.
-

See if this helps:
http://r937.com/relational.html

 

[Timothy Andrepont]

SOS,

Thanks so very much for the article. It will be a great help to me.

I am still working on digesting normalization. Actually it is fascinating. I will begin to develop a normalized system from all the fields in current table.

In the mean time is the best way to move data from existing tblTotal to normalized tables to create queries and then make them into tables?

Thanks again.

Timothy

 

[Galaxiom]

Create your normalized table structure.

Then use Insert (AKA Append) queries to move the data into the new tables.