Field name as variable

[PeterWieland]

This question has been posted a couple of times before, but does not seem to have been fully answered.

I have database for recording student assesments. There is one record per student/subject, with 6 fields, one for each of 6 assesments throughout the year i.e

Name,Subject,Attain1,Attain2,Attain3,Attain4,Attain5,Attain6

Each student has one record for each subject. ie

Mary,Maths,A,B,B,A,A,A
Mary,Science,C,B,C,B,A,B
etc.

I have a crosstab query which produces a total of each grade for ONE column ie

Name,A,B,C,D,E

Mary,2,4,3,0,0

ie Mary got 2 A's, 4,B's and 3 C's

This all works fine.

Now the question!

The crosstab has the following PIVOT:

PIVOT qryGrades.Attain2 In ("A","A*","B","C","D","E","F","G","N","Na","U");


How can I have the field 'Attain2' assigned by a variable, so that the one query could be used for any of the 6 assesments. I have over 100 queries in the full database, and do not want to multiply this by 6 to give one for each module.

Hope this all makes sense,

Peter Wieland

 

[Rich]

Normalise your structure first, you don't need six separate fields to store assesments. Search here for posts on normalise/normalize

 

[PeterWieland]

Hi Rich,

I tried the Normalization process, but because of the way the data is entered, this structure is the most efficient.

Basically, the data is entered by class, spreadsheet style, showing all the kids in the class, with each of the 6 assesments. I tried setting up the table with one line per student/subject, with a column for assesment grade and a column to indicate which of the 6 assesments it is, but the queries to produce the class lists were too complicated.

Which ever way you look at it, the data is entered one way, by class, and used another way, by student, and I will get the same problem either at entry time or report time.

Using a variable for the field, if possible, seems to be about the best way of doing it.

Peter

 

[Fizzio]

I would back Rich up. If your data structure is normalised correctly, it will be a less of a headache overall. You said that a normalised structure made if difficult to get class lists - If this is so, your data structure is not effective enough. You are now experiencing the problems with non-normalised data. I would reconsider the normalization process.

 

[Rich]

It can be frustrating not being able to enter data in a spreadsheet/crosstab manner, I think flexgrid is the third party add in you need