Compare input to table of ranges

youknow

New member
Local time
Today, 02:41
Joined
Jun 19, 2012
Messages
4
I have a form that takes user input (about 20 text boxes) and writes to fields in a table. Form is locked to one record per instance.

I need to compare the user input to values in a table. The table is structured so that each record is relative to one specific field in the table (also one specific textbox on the form) and each record has three fields with values that determine a 'range'.
What I need to do is compare the user input (for each field) to the associated ranges in the table to 'grade' the input. The result will be to color textboxes, labels, etc. on the form based on this grading.

What is the most efficient way to do this?
Should I read the grading into an array and then compare each field (textbox on the form)?
Should I compare one field at a time to one specific record? (seems code intensive and very unforgiving when changes come)
Help. Thanks.
 
Hello there and welcome to the forum.
I don't understand your post. What does this mean?
The table is structured so that each record is relative to one specific field in the table
What does this mean?
What I need to do is compare the user input (for each field) to the associated ranges in the table to 'grade' the input.
Do you have a formula you can show to demonstrate? Can you demonstrate your actual table structure?
Cheers,
 
This is more of a conceptual question. It will surely be followed up with coding questions.
Users input KPI data to a form. There are 20 + fields (or KPIs), here are some examples: [Number of Accients], [Number of Cases], [Number of Reports], etc.

I have a table with these fields identified AND each has a grading system. For example (sorry for all of the '....', but the forum compressed spaces):
KPI...................................Grade 1.....Grade 2......Grade 3
-----------------------------------------------------------------------
[Number of Accidents]....5.................3.................0
[Number of Cases]...........4.................2.................0
[Number of Reports]........3.................1.................0

I need to compare the user input on a form to the grading system in the table. The grading system will score each KPI and apply a color system (Red, Yellow, Green) to visual indicators on the input form.
I can code all of the logic and make the visual indicators do their thing, but
I am asking for direction to perform this with VBA in the most efficient manner.
At first, I considered hard coding each text box the user enters the number into.
But, it seems that an array (aided with variables and loops) may be more sensible.

Thanks.
 
Last edited:
Your grading table should be normalized like this:

tblKPIs
--------
KPI
KPIValue
Grade
 
Last edited:
Thanks spikepl for the suggestion. However, the KPI Value the user enters goes into a separate table. So, the KPI Grading table contains only the KPI and the grading values.
 
Last edited:
Spike is right. It seems you are thinking in Excel and working in Access, which is hazardous. I recommend you Google 'normalization' and do a little reading before you proceed using databases.
 

Users who are viewing this thread

Back
Top Bottom