Access Query: Too many fields defined. (1 Viewer)

T

Timothy Andrepont

Registered User.

Local time

Today, 10:08

Joined

Apr 26, 2013

Messages

37

• Jul 18, 2013

• #1

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

 

Attachments

• ZipPSAssm03.zip

  578.5 KB · Views: 128

M

Mihail

Registered User.

Local time

Today, 17:08

Joined

Jan 22, 2011

Messages

2,373

• Jul 19, 2013

• #2

Can't open in A 2007

 

G

Galaxiom

Super Moderator

Staff member

Local time

Tomorrow, 00:08

Joined

Jan 20, 2009

Messages

12,851

• Jul 19, 2013

• #3

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.

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 10:08

Joined

Feb 19, 2002

Messages

43,257

• Jul 19, 2013

• #4

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.

 

Last edited: Jul 19, 2013

T

Timothy Andrepont

Registered User.

Local time

Today, 10:08

Joined

Apr 26, 2013

Messages

37

• Jul 19, 2013

• #5

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

Registered Lunatic

Local time

Today, 07:08

Joined

Aug 27, 2008

Messages

3,517

• Jul 19, 2013

• #6

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

Timothy Andrepont said:

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.
-

Click to expand...

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

 

T

Timothy Andrepont

Registered User.

Local time

Today, 10:08

Joined

Apr 26, 2013

Messages

37

• Jul 19, 2013

• #7

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

 

G

Galaxiom

Super Moderator

Staff member

Local time

Tomorrow, 00:08

Joined

Jan 20, 2009

Messages

12,851

• Jul 19, 2013

• #8

Create your normalized table structure.

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