Having a Weigted Rating System in a Form

[ryanmce92]

Hi there,



I am in the process of creating a supplier database which will hold all of their contact details and the products which they supply.

I have created a form "Add a New Supplier", in this form I have the usual contact details fields. At the bottom of this form I would now like to create a 1-10 rating system for each indvidual supplier. I want to have 3 ratings "Delivery Time", "Product Quality" and "Past Experience" (there will be a notes section to explain).

I would like the 3 "ratings" to deliver one single rating based on a weighted average e.g 40%/40%/20%

Is there anyway which this would be possible or is it just more bother than it is worth going in to?.

[orange]

Please describe this with an example

a 1-10 rating system for each individual supplier. I want to have 3 ratings "Delivery Time", "Product Quality" and "Past Experience"

I'm sure its "do-able", but we need some details to see where the starting values come from and how objctive they are.
How do you evaluate "Delivery Time", "Product Quality" and "Past Experience"?

[ryanmce92]

Please describe this with an example

I'm sure its "do-able", but we need some details to see where the starting values come from and how objctive they are.
How do you evaluate "Delivery Time", "Product Quality" and "Past Experience"?

As I'm doing this for a manager I am only using these as test fields, I would just like to know how I would go about creating something like this, if the rating system involved the 40:40:20 ratio I described prevously?

[John_G]

Assume your three ratings are A, B and C:

(40*A + 40*B + 20*C) / 100.0 should give you what you need.

[ryanmce92]

Assume your three ratings are A, B and C:

(40*A + 40*B + 20*C) / 100.0 should give you what you need.

Is there anyway to make this be automatically calculated once the three values are entered?.

Would I have to create a button to run VBA to calculate the overall rating?

[John_G]

I would suggest using VBA to calculate the average after any one of the three values are entered or changed, in case a user makes changes after all three are entered.

You would put the same line of code in the After Update event of each one, something like this (using your own form control names):

[Weighted_Average] = (40*[Delivery Time] + 40*[Product Quality] + 20*[Past Experience] ) / 100.0

You could use a button to calculate the overall rating, but you would have to find a way of ensuring the user actually clicked it after making changes. Automatic rating recalculation is better, IMO.

[ryanmce92]

I would suggest using VBA to calculate the average after any one of the three values are entered or changed, in case a user makes changes after all three are entered.

You would put the same line of code in the After Update event of each one, something like this (using your own form control names):

[Weighted_Average] = (40*[Delivery Time] + 40*[Product Quality] + 20*[Past Experience] ) / 100.0

You could use a button to calculate the overall rating, but you would have to find a way of ensuring the user actually clicked it after making changes. Automatic rating recalculation is better, IMO.

I will be handing out guides with this database, my end users wouldn't exactly be the most computer literate, I'll have a tutorial on how to use it etc.

This looks like it will work, is there anyway for it to be automatically entered rather than having to click a button?.

[rpeare]

it's not entered, it's a calculated value which can be populated any time you open a form or a query, I think that's what John_G is saying. So for instance if your form is based on a query you can add the calculated field to that query, then whenever you navigate to a new record the calculated value appears.

[John_G]

Putting the code in the After Update event of each of the three rating boxes will result in automatic recalculation each time the values are changed.

You don't need the button at all.

Just make sure the Weighted Average control (field) cannot be updated by users (Locked property = Yes), and that the users cannot tab into it (Tab Stop = No)

[ryanmce92]

Thanks for all your help, seems to be working perfectly!!

Now how do I close the thread? haha