Query for Calculating a Rating depending on Multiple Criteria

manix

Registered User.
Local time
Today, 21:56
Joined
Nov 29, 2006
Messages
100
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.

Code:
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
 
So your query has the following fields: [De-Merit Marks] and
[No Del with Major Faults]

What does the sample data look like? Seems like you'd have to have a customer field which would be a primary key - ?
 
So your query has the following fields: [De-Merit Marks] and
[No Del with Major Faults]

What does the sample data look like? Seems like you'd have to have a customer field which would be a primary key - ?

Here is what the query looks like as it stands at the moment. It is really finding a away to get the major delivery fault thing do downgrade the alphabetical rating as described in my previous post.

Location name is the supplier's name. I am not too worried about the error, as this only occurs because I have entered any data in for that particular supplier (although I would like to how to mask this, as there maybe times when a supplier does not supply for a given period?)

Basically the amount of deliveries is counted and taken from another query, the demerit marks are calculated in another query and the deliveries with major faults are also calculated in a nother query. This query brings them together and calculates the rating.

TIA
 

Attachments

  • Query 1.JPG
    Query 1.JPG
    54.2 KB · Views: 116

Users who are viewing this thread

Back
Top Bottom