Query For Calculating A Rating Depending On Multiple Criteria

Hi All,

I have fought hard to try and figure this out and I have gotten 50% there.

I have a query that generates the following calculated fields through numerous calculations etc....:

[De-Merit Marks]
[No Del with Major Faults]

Now the way it should work is that a supplier gets a rating (A-D) depending on both their De-Merit Marks score and the amount of deliveries with major faults. I used the following function in the query and it does the de-merit mark grading.

Rating: IIf([qry_vend_rating2]![De-Merit Marks]<=10,"A",IIf([qry_vend_rating2]![De-Merit Marks]<=30,"B",IIf([qry_vend_rating2]![De-Merit Marks]<=50,"C",IIf([qry_vend_rating2]![De-Merit Marks]>=51,"D",""))))

However I have no idea how to integrate the Major Faults part.

Basically:

0-10 = A
11-30 = B
31-50 = C
51-100+=D

If the supplier has one major fault, the rating should drop one from that reported using just the de-merit score. Two means it drops two ratings (so if an "A" on just the demerit, they would drop to a "C" and so on. Obviously a supplier cannot drop below a "D".

Can anyone suggest how these maybe acheived? I fear it requring VB code, but I am not that good with VB hence why I have relied on queries for this.

TIA