in need of modification

[Pharcyde]

Hey all!

I have a table as part of my db that needs modifying. It was ok but the boss has decided it needs alittle more...

I have the fields: StudID - AssID - ResultID - AssDate - DatePaid - AssessorID with the problem being in the DatePaid field...

Only resit assessments require payment, therefore the first time an assessment is taken it cannot be paid for.

I also have a couple of queries running that bring up the amount of paid/unpaid resit fees. These are incorrect now as they also bring up the 1st instances of failed assessments.

My first thoughts were of a resit 'flag', or count for the # of times assessments have been taken, but I cant think of how to implement it...

Could anyone help me. or more likely come up with a better solution??

Cheers, Lee

 

[The_Doc_Man]

In general, there are only two ways to handle this problem.

1. - the brute force method - include a new flag that says "YES/NO" this is a RESIT case.

2. - the preferred, more elegant method - determine a way to know if the record represents a RESIT case. Then include all your data in a query that includes the formula that tests the case for you.

If the issue is that the first time is freebies, then perhaps what you need is a DMin that looks for the earliest date for the given student for that particular event. Put that in a query and have a yes/no test that says "This date = DMin function's date"