Calculation based on yes/no boxes

[extraolivesplea]

Here's what I'm trying to do:
Students have a form with 9 statements in - they check 3 of them. The underlying table uses yes/no for the statments. Is there a better structure perhaps? 3 of the statements are worth 1 point, 3 2 points and 3 3 points.
For each student I want to calculate their ovreall score.
Have built a solution to this in both Access and Excel & while I can get the calculation I want easy as in Excel, I prefer some of the other features of Access and think this will be the best programme in the long run.
Hope someone can help!
Thanks

 

[The_Doc_Man]

This structure is not normalized. You have grounds for at least two tables.

Splitting the tables would allow you to do the grading pretty quickly with some SUM queries and the knowledge that a YES in a YES/NO box counts as a byte of -1 and a NO counts as 0. So the sums of YES/NO for the correct answers (not the actual answers, of course) is your score, not scaled to 100%

Look up normalization issues. You have two keys here: Student and Question. Grounds for two tables, one of which has prime key (PK) of student only, the other has PK of student AND question as a compound key.

Once you get a good table structure going, you have a bazillion ways to skin this particular cat.

 

[extraolivesplea]

calculations on yes/no box fields

Hi!
Sorry for the confusion but there are 2 tables. 1 holds students details, 1 holds the statements - each statement has a separate field name.
Have now sorted out the calculation issue, but having a syntax problem with the code to allow only 3 check boxes to be ticked at any one time.
If you have any suggestions on this I'd be very grateful!
Thanks for your time

 

[Mile-O]

Please detail the structure of your tables. It makes it easier to see what you have done and what advice to offer.

 

[extraolivesplea]

validating check boxes & calculating

there are 2 tables:
tbl1 is for student details are contains fields for:
ID (pk), 1st name, surname, tutor grp, ks2 aps score
tbl2 is for statements:
ID (PK), 9 separate fields for statements (yes/no), self-assessed level (number - calcualtes based on which statements checked), teacher level (number inputted manually), ks3 target (number - manual entry), student comment (memo) & teacher comment (memo), calculation (number)

I have sorted out the calculation but am having a bit of trouble only allowing 3 statements to be checked at any one time.
The queries I want to run work and on the whole I am very happy with the system.