Finding values between two parameters

[Guest]

I think I'm trying to do something that may only be possible in Excel, but
I'll ask anyway.
I'm creating a database that keeps track of internal audits undertaken at my
company. Part of this audit is to allocate a percentage score and then
allocate text that describes that score. For example, a score of 47% would
recieve the description "Needs Improvement".
I would like to have Access automatically assign the correct text for the
score achieved. The ranges are:
0-20 "Unacceptable"
20-50 "Needs Improvement"
50-70"Acceptable"
70-90 "Good"
90-100 "very Good

These definitions are stored in a seperate table. I am creating a query that
would collate these scores from tables and turns them into percentages. The
idea is that this query ( or form/report), performs something similar to an
Excel "If" or "lookup" function and assigns the text statement depending on
which of the above ranges fit in with.

I would appreciate all the help I can get

regards

Phil

 

[Guest]

You can do that in forms, reports, and queries. All you need to do is assign
some value to a field in the table you keep the scores in that would the same
as the value in a field in the table where you keep the descriptions. That
way, you can simply join the tables in a query any time you need them. For
example, in the table for scores, have a field called SCORE_RANGE that would
be a value of 1 - 5 depending on the ranges you described. Now, you can have
the same field name in the Descriptions table with the 1 - 5 corresponding to
the score. So when you join the tables in a query on that number and include
the description field in the query, you will get the correct description.

 

[Guest]

Thanks for your quick response.
I'm not sure how I could get Access to assign a value of 1-5 in order to
link the tables. I would want the user to type in the scores, and access then
allocates the text description. If I understand what you are saying, the
method you describe would need the user to then allocate a number as well as
the score in order to get the text description.
How do I get Access to allocate the 1-5 numbers?

(Sorry if I'm being a little slow)

Cheers

phil

 

[Guest]

Don't worry about being slow. I will type very slowly to make it easier to
understand

If you are entering the scores in a form, you can assign the numbers
programmatically.

I would create a hidden text box on the form that would be bound to the
score range field in the scores table. Then in the After Update event of the
text box where you enter the score, determine the correct value and put it in
the control for the range.

Select Case intScore
Case Is <= 20
Me.txtScoreRange = 1
Case Is <= 50
Me.txtScoreRange = 2
Case Is <= 70
Me.txtScoreRange = 3
Case Is <= 90
Me.txtScoreRange = 4
Case Else
Me.txtScoreRange = 5
End Select

 

[Guest]

Ahhhh!! I think thats the answer.

Many thanks for help and patience.

Phil