Pass and Fail

Static_Cling

So I have a table where results of a test are kept. A user can take the test multiple times if they fail, and the results must be kept. So for one test a user may have 4 Fails and 1 Pass...now, Im trying to report off of this table to see who has passed and failed the test....so the pass rpt is easy, select from results where pf = 'P'
However, not so easy for the failed, b/c I keep getting users who have passed the test...is there a way to say Select from results where pf (is failed but not if P is present)???

Thanks for the help

_RL_Ian

Can you be a little more clear with what you are wanting to do.

I didn't understand what you meant by this:

Select * from results where pf (is failed but not if P is present)???

Static_Cling

sure, lets say I have test1. User takes test1 and Fails. An F is placed in the score field. Users takes the test again, and again fails, so now another row is inserted into the table with F in the score field. On their third attempt, user passes test, and a third row is inserted into table, P in the score field. So now for this one test, I have three scores, two fails and a 1 pass. Has to be this way so it can tracked how many times user took test. So now, I need to report who has passed and who has failed. Sounds easy enough, but I need to get those users who have taken the test and failed, but then passed off of this report....so basically:

Select * from results where score = 'F' [what I need here though is something that says F if there is no P for the user/test combination....

TobyRT

What are the fields in the db?

My inclination might be to extract all records with P and F and use count() and a couple of if() statements to parse the results.

Static_Cling

I'm basically just pulling three fields:

username, coursename, score

score is where Pass or Fail is located...

TobyRT

but if I take the test 3 times and pass on the third, surely the 'score' field is overwritten with a P, so there's no way to access the test history?

What are all the fields in the db?

_RL_Ian

You should add a DATETIME field to your records and then you can select the newest score (grouped by username) and you will have the last result for each user.