Allowing updating

[Pharcyde]

Hi all.

I have a query linking 2 tables: Student data & Student Assessments.

For eachstudent, I would like to count the number of assessments they have passed (using dcount).
Then, if they have passed all three i would like the (yes/no) accredited tickbox to be enabled.
In other words it can only be ticked if they have passed three assessments...

Also, some notification that all assessment have been passed, like maybe a label that appears once a condition has been met?

How would i go about doing this??

P.S: Does that make any sense??

 

[Mile-O]

I have a query linking 2 tables: Student data & Student Assessments.

Shouldn't you really have three tables?

A "junction" between a Students table and an Assessments table.

 

[Pharcyde]

well, i trust your jusdgment, but am not sure what you mean exactly....

 

[Mile-O]

tblStudents
StudentID
Forename
Surname
Other studenty personal details

tblAssessments
AssessmentID
AssessmentName
Other assessment specific details

tblStudentsToAssessments
StudentID
AssessmentID

In the third table the two fields are foreign keys to their respective table - you then make these two fields the primary key for that table.

That way, your database becomes normalised. I'm guessing you had three fields relating to their respective assessment.

No you can use your DCount on the third table:

i.e.

DCount("[AssessmentID]","tblStudentsToAssessments", "[StudentID] = " & Me.cboStudents)