Data Validation Rules

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On one table there are four fields:
StudentID, text
ExamType, text
ExamID, text
Attempt, text
I have an index on the table to prevent a combination of these four not
having a duplicate entry. There are two circumstaces where ExamID is not
required, and therefore not monitored by the index (Mid Term and Final Exam)

I was thinking of using a DoCmd.RunSQL statement on the before update event
of the record, to see if a record already existed for this Student, ExamType,
and Attempt to promt a message and prevent the record from saving.

A related issue, is to prevent selecting "2nd" attempt, if there is not a
"1st" attempt on record for this Student, this ExamType, and this ExamID(if
applicable).

I would appreciate any referece where I can learn more on how to do this
level of data validation.

Thank you,
Renee
 
Hi, Renee.

My first suggestion would be to make all of your text fields numeric,
creating the Table Structures:

Students
-----------------
StudentID AutoNumber (Primary Key--PK)
FName Text
LName Text
Phone Text
etc.

ExamTypes
--------------
ExamTypeID AutoNumber (PK)
ExamType
etc.

Exams
----------------
ExamID AutoNumber (PK)
ExamType Number (Foreign Key [FK] to ExamTypes)
ExamName Text
etc.

MainTable (or StudentAttempts)
----------------------------------------
MainID AutoNumber (PK)
StudentID Number (Foreign Key to Students)
ExamID Number (Foreign Key to Exam)
Attempt Number
Result Number
etc.

You don't need the ExamType in the Main Table, because Exam and ExamType are
related tables. To make things simpler, I think it would also be easy to add
"Midterm" and "Final" into the Exams table. Then your index can do the
monitoring for duplicate entries.

For your Main table form, you can use combo boxes to simplify and ensure the
consistency of the data entry of the Student and Exam fields.

To ensure the next number is in sequence, you can use the DMax function to
obtain the maximum number currently in the database for this student for this
exam. Alternatively, and preferably, split the Attempt and Result out into a
separate table, reflecting the one-to-many relationship between Student/Exam
and the attempts he makes on that exam. Then you could have a main form
based on a query, and a subform that displays all the attempts on that exam
so far.

Hope that helps.
Sprinks
 
Back
Top