Weird Update Query Question

[Carmen]

Hi All,
I have a table called PassedExams that have fields for SSN, ExamID, Oral, Reading, Written, ExamDate. There are only two exams but each one has the 3 parts to it (oral, reading, writing). They are pass/fail. If a person fails one (or more) parts, they can retake those parts at a later date. Therefore each SSN can have a multiple entry for each ExamID. So I might have something that looks like this:
SSN ExamID Oral Read Written ExamDate
SSN# 1 P F P 01/01/02
SSN# 1 N/A P N/A 02/01/02
Here is my problem..on a separate form (Qualifications)I have a check box and a date field for each of the 2 exams to show if a person has passes all 3 parts and when. So what I want is an update query that will check the box and put in the exam date whenever a person finally passes all three parts. I'm not sure how to structure it so that it looks for "P"'s in different records and then gets the Max Date. Or is this even possible? Thanks for your help!

[This message has been edited by Carmen (edited 02-13-2002).]

 

[Alexandre]

You do not need a query for that, you can do it using for example the after update event of you check boxes. For example, something like:

If (Me.Exam1Part1 AND Me.Exam1Part2 AND Me.Exam1Part3) Then _
Me.Exam1PassedDate= iif(Me.Exam1PassedDatePart2 >= Me.Exam1PassedDatePart1; _
iif(Me.Exam1PassedDatePart2 >= Me.Exam1PassedDatePart3; Me.Exam1PassedDatePart2; Me.Exam1PassedDatePart3); _
Me.Exam1PassedDatePart1)

where Me.Exam1Part1... are your check boxes
Exam1PassedDatePart1... are the associated dates

You would have to foresee also the case where a user selected the three textboxes (mistake), then de-select one of them.

Alex

[This message has been edited by Alexandre (edited 02-13-2002).]

 

[Carmen]

Alex,
Thank you for your reply but I'm not sure that's exactly what I need. The 3 exam part fields are text where I would enter either a "P" for pass or an "F" for fail or "N/A" and then I have one date field for the date of that exam. So if a person needs to retake a part of the exam they failed, that would make a new record in the table for that person with a new exam date. When, after one, two, three, or however many tries, the person finally passes all three parts of the exam, I want to automatically check a box labelled Exam Passed and fill in the final passing date in a field called Date Exam Passed. Does any of this make any sense?

 

[David R]

I'd like to propose an alternate structure for your table.

tableExams:
PrimaryKey, SSN (Indexed, not Unique), ExamID, ExamPart (Oral, Reading, Written), Result (Pass/Fail...what does N/A mean?), ExamDate. You can make ExamPart and Result dropdown combo boxes.

You should be able to use this a little more neatly to find out if someone has passed all three parts, and your table is more flexible (ex., you can add a Comprehension section to the test without totally reworking your table structure).

I would put a check in the BeforeUpdate event of the Result box to make sure they haven't already passed this section for this exam (provided they don't have to retake it, which is how I understood it).

Private Sub Result_BeforeUpdate(Cancel As Integer)
    
If (DCount("SSN", "tableExams", "tableExams.SSN = '" & Me.SSN & "' AND tableExams.ExamID = " & _
      Me.ExamID & " AND tableExams.ExamPart = '" & Me.ExamPart & _
"' AND tableExams.Result = 'Pass'") = 1) Then
        MsgBox "Please double check, " & Me.SSN & " has already passed the " & _
Me.ExamPart & " section of Exam " & Me.ExamID & "."
        'Note to purists: I am not putting Cancel = True here
        'because it interferes with correcting existing data
    End If
End Sub

To check at the end of putting a new record in if the person has now passed all three parts, with this table structure it would be something like:

Private Sub Form_AfterUpdate()
    If (DCount("SSN", "tableExams", "[SSN] = '" & Me.SSN & "' AND [ExamID] = " & _
      Me.ExamID & " AND [Result] = 'Pass'") = 3) Then
        'Do whatever you need to when a person finishes a test completely
    End If
End Sub

Hopefully that will get you started. You don't need to physically store the 'Date finished' in a separate field because you can query it later at any time.

Please post back if I've been confusing. I'm not sure what level of coding you're at, so I actually did a mock up of this. It doesn't account for all eventualities, for example updating the date field on an existing record where the person has passed all three sections will trigger the 'Do whatever...' section again.

David R


[This message has been edited by David R (edited 02-13-2002).]