469,342 Members | 6,842 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

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

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.
Jan 12 '11 #1
11 3577
Rabbit
12,516 Expert Mod 8TB
You could use nested IIf() to get what you want. But your ranges are incorrect; They shouldn't overlap like that.
Jan 12 '11 #2
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?
Jan 12 '11 #3
Rabbit
12,516 Expert Mod 8TB
Basically, a nested IIf would be of the form:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 12 '11 #4
so how would you put query into this instead of IIF?
Jan 12 '11 #5
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
Jan 12 '11 #6
Rabbit
12,516 Expert Mod 8TB
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.
Jan 12 '11 #7
Rabbit
12,516 Expert Mod 8TB
If I had a score on a test and wanted to show a letter grade, I would do
Expand|Select|Wrap|Line Numbers
  1. IIf(Grade>=90, "A", IIf(Grade>=80, "B", IIf(Grade>=70, "C", IIf(Grade>=60, "D", "F")))
Jan 12 '11 #8
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?
Jan 12 '11 #9
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]
Jan 12 '11 #10
Rabbit
12,516 Expert Mod 8TB
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?
Jan 12 '11 #11
Jerry Maiapu
259 100+
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
Jan 13 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by xunling | last post: by
2 posts views Thread by Jet Leung | last post: by
reply views Thread by David | last post: by
1 post views Thread by hjc | last post: by
3 posts views Thread by pockydondon | last post: by
7 posts views Thread by CWilliam912 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.