Fields in a query

[sjcowden]

I am trying to create a query but when I run it I get the message - query too
complex. Am I limited to the number of fields I can incorporate into a query
or is there a way round this. At present I have over 150 fields. I want to
create a query that illustrates how pupils are progressing and applying
themselves from when they join school until they leave.

Any and all advice greatly received.

Many thanks.

 

[Duane Hookom]

Most of us old timers think anything more that 25-30 fields is too many.
There are lots of reasons for the "too complex" message. I would prefer to
not attempt to describe them all and rather suggest you either search the
web or provide more information here such as:

-the SQL view (this scares me if you have 150 fields ;-)
-the data types of fields used in any calculations
-your table relationships
-a justification for 150 fields

 

[sjcowden]

Thanks for your reply. I am trying to bring a range of information detailing
how students have progressed from the first year of secondary education to
their last - 5 years in total. The data includes progress grades - A, B , C
etc for each subject. There are approximately 10 subjects. The data includes
effort grades 1, 2, 3, etc. In a single year there would be 12 progress and
effort grades for each subject. There is also attendance data to be included.
I obviously want to compare student performance term by term, year on year.
Hence the inclusion of so many fields. Perhaps there is another way to
achieve this.

I want to be able to present this information in a form so that I see how
each student has performed during their time at school.

I have managed to do this before covering only the last two years of
secondary education. Mind, I am no expert!

I would appreciate any additional advice.

Regards

 

[sjcowden]

I have just discovered that there is a limit of 255 fields per query.

 

[Duane Hookom]

I think your table structures are highly un-normalized. In a normalized table
structure, I would expect each progress grade to be recorded in its own
record. I believe (in the words of fellow MVP, Jeff Boyce) you have
"committed spreadsheet".