I need help to implement this function

[keek2a4]

Hello Everyone,

I need to implement a particular Function. Let me explain, with a simple example.

I have a table called tblExamDetails with four fields: StuID, Subject, Result, Status

I want to implement a function that when I change the result of a Student to Fail, it updates Status to 0 and when I change the
result of a student to Pass, it updates Status to 1. However in my database, a student can keep resitting a subject till they pass, so say if student ID "001" has resitted subject "Maths" two times and Passed the third time, it updates status = 1 for the Pass record but also updates status = 1 for the past two failed attempts.

So for example say it this is my table record:




StuID Subject Result Status
=====================
001 Maths fail 0
001 Maths fail 0
001 Maths pass 1
001 Science pass 1
001 English fail 0


After running the function, the table should be


StuID Subject Result Status
=========================
001 Maths fail 1 (this is updated)
001 Maths fail 1 (this is updated)
001 Maths pass 1 (this is updated)
001 Science pass 1
001 English fail 0

The logic I need to implement in VBA is something like this:
When result = pass then change all status to "1" WHERE stuID = this.stuID AND subject = this.subject

Can someone plz guide me on this!

Thanks very much,

[mattj]

Why would you do this? This would essentially be storing identical information twice in the same table. Also, it violates normalization rules by having one field in the table dependant on another field in the same table. You only need one of the fields - the other can then be calculated whenever you need it.

HTH
Matt

[keek2a4]

Hi Matt,

Thanks for your reply.. Yes you are right with regards to the violation of the Normalisation rule but I have already created the whole application
with poor database design (my bad). Now it would make things much more difficult if I change the table layout.

To make each record unique I also have a date field that makes the stuID, subject, and date a unique record and act as a primary key together.

I would have to violate the normalization rule and update the status field of previous resits when the student passes, as I am on the last stage of
completing my application. Can this till be done?

Thanks,
Ket

[mattj]

Even with the poor design in the rest of the database, there is not need to store this in the table. Just store the numeric value, then in a query create a new column that generates the pass/fail text:

MyNewField: Iif(NZ([Status],0)=0,"Fail","Pass")

And here's me getting on my little soap box:
If you know the design is bad, you're going to spend more time creating workarounds andd difficult solutions to what what be relatively simple than it would to correct the structure now. Might as well do it before the application is rolled out then wait until it becomes to complicated and unweildy.
And most experienced posters will tell you that while you can "cover up" most table structure problems by throwing enough code at them,
it is rarely worth the effort, and with time the problems with the application just get progressively harder to fix via code (and said code usually gets pretty messy, as well).
It is almost always much better to correct table structure problems first.

Matt

[mikerickson]

You could use one column of numebers, formatted: "pass";"";"fail".
Entering 1 would show "pass", 0 "fail"