How to group and assign value to column data by range of score?

[David Sanchez]

We are doing a database of gun quals, its fairly simple in theory but if a shooter scores 1-220 he does not qual, if he shoots 221-228 he is considered marksman, 228-240 is expert.

So i would need it to basically say
1-221 = NQ (No Qual)
221-228=M
228-240=E

So it would look in the awards colum and see the number and give e,m,nq as an output depending on the number.

[Rabbit]

You could use nested IIf() to get what you want. But your ranges are incorrect; They shouldn't overlap like that.

[David Sanchez]

ok so nested can you elaborate a little more i understand the if statement and overlapping but how do you input it into the table?

[Rabbit]

Basically, a nested IIf would be of the form:

IIf(expression, trueValue, IIf(expression, trueValue, IIf(expression, trueValue, falseValue)))

Normally you don't want to input a calculated field into a table. It's better to use a query and have the query calculate on the spot when needed.

[David Sanchez]

so how would you put query into this instead of IIF?

[David Sanchez]

on the IIF would expression be the formula and how would you write it per say the range 221-228 true value would be M 229-240 would be E and anything 1-220 would ne NQ

[Rabbit]

No, you would use the IIf in a query. You wouldn't, or rather shouldn't, use it to put the data into the table.

Use the IIf, use the query, put them together.

[Rabbit]

If I had a score on a test and wanted to show a letter grade, I would do

IIf(Grade>=90, "A", IIf(Grade>=80, "B", IIf(Grade>=70, "C", IIf(Grade>=60, "D", "F")))

[David Sanchez]

I tried it this way again im sorry new to this:

= IIf (1-220, NQ, 221-228, E, 228-240, M)

How off am i?

[David Sanchez]

thnx again i changed the numbers and here is what it looks like but its saying a invalid character or comma:

= IIf( [Weapons Quals]![Navy Handgun Qualification Course(NHQC) Score] >=228, "E", IIf( [Weapons Quals]![Navy Handgun Qualification Course(NHQC) Score] >=227, "S", IIf( [Weapons Quals]![Navy Handgun Qualification Course(NHQC) Score] >=203, "C", IIf( [Weapons Quals]![Navy Handgun Qualification Course(NHQC) Score] >=179, "NQ", "F")])

Thetables name is [Weapons Quals]![Navy Handgun Qualification Course(NHQC) Score]

[Rabbit]

Are you doing this in a form bound to the table? You don't need to reference the table name. Also, you need to close all your IIf(). If you have three IIf(, then you need 3 parentheses at the end. Also, why do you have more ranges now than you first specified?

[Jerry Maiapu]

Hi David, a just got into this forum today and after reading it,I hope the following hint would direct you in acheiving what you seem to explain.

First thing first:

1. You need a QUERY to give you the ranking of shootings.
2. A query displays results based on TABLE with some data on it, so obviosly you need a table with the shooting scores for each officers. So I assume that you have say 1 coulmn for officer names and maybe another column for shooting scores.

3. Now you need a query to pick the names and coressponding scores from the table and display the results based on the criteria you specified in your question. ( Note that Query is moreleass like a calulator that manupilates the data stores in the table. Reports/forms can be used to display this results.
You can also use forms to edit and store new information into the table.) Therefore, you'll need to use IIF clause in the query. Just bare in mind that IIF can also be used in text boxes in reports and forms )

Now back to your question:

From your postings I assume the the name of your table is Weapons Quals and the column names in the table are: Navy Handgun Qualification Course(NHQC) Score. ( Note that long names are not a good practise try to give a short name for columns:

Create a query based on the table and then select the Officer name field (ie column)and shootingscore field from the table. When you run it you will see the names of the officers and the corresponding scores of each. Post back when are ready to continue from here so that we'll get through the rest of the question with eapecially the IIF clause to display the results.

Time is catching up with me to go home. Got no access to internet at home. I'll get back to you 2morrow morning.

Cheers!
JM