admission priorities ranking system

[damsel]

I have 3 fields. FamilySize, IncomeLevel, and Rank.
It is based on a Admission Priorities chart like this.

The family will get ranked no 1 if family size 2 and income level $364. Or if family size 3 and income level $390. Or if family size 4 and income level $ 433. Or family size 5 and income level $ 503.

Rank 2 if family size 2 and income level $ 400. If family size 3 and income level $ 429. If family size 4 and income level $477. or family size 5 and income level $553.

The chart has 66 ranks and up to 12 family size.
I want when I plug in FamilySize "2" and IncomeLevel "364" the Rank will automatically shows up as "1"
I tried to make a lil something like this.

Case( Family Size = 12 and Income Levels >= 4875 or Family Size = 11 and Income Levels >=4777 or Family Size = 10 and Income Levels >=4680 or Family Size = 9 and Income Levels >=4582 or Family Size = 8 and Income Levels >= 4485 or Family Size = 7 and Income Levels >= 4387 or Family Size = 6 and Income Levels >= 4290 or Family Size = 5 and Income Levels >= 3770 or Family Size = 4 and Income Levels >= 3250 or Family Size = 3 and Income Levels >= 2925 or Family Size <= 2 and Income Levels >= 2730, 66,

and so on.

Is this possible or am I going on the right direction?
Any help is greatly appreciated

 

[Jack Cowley]

Attached is a quick Access2000 demo to see if this approach would work for you. If you have Access97 let me know...

hth,
Jack

 

[damsel]

Hi Jack

Thank you for the help. I'm using Access 2002. It seems that the zip file is empty. Can you please kindly attach it again ?

 

[Jack Cowley]

Oops! Sorry about that... Give this one a try...

Good luck...

Jack

 

[damsel]

Hi Jack

Thank you for ur kind help.
The form works great.
However, for example, if I have a customer with family size:2 and income level of $375 for admission, this customer would rank as no 2 (based on the admission chart).
Is there a way that the form would automatically say rank no 2 ?
Sorry for the endless questions

 

[Jack Cowley]

In the code in the After Update event of the second combo box is this line of code:

Me.Text10 = R

If you change that read:

Me.Text10 = "Rank no " & R

then you will get the response that you want.

I am glad to help you out so if you have other questions just ask....

Jack

 

[damsel]

Um Jack...

The form doesnt' really work the way I wanted it to be.
I think I explained it wrongly.
What I wanna do was to have a base from a priority ranking chart. Like

Rank 1
FamSize 2
Income $364

Rank 2
FamSize 2
Income $400

so if for example i have a customer with famsize of 2 and income level $389... s/he would still fall into category rank 1.

Is it possible to do this?
Thank you so much again.

 

[Jack Cowley]

Ah ha! Now I think I understand what you want... In your example the person is just below the $400 minimum so she/he falls into Rank 1 but if she/he had an income of $401 then she/he would fall into Rank 2.

Would it be correct to say that a family of 2 with a income between $0 and $399 would be ranked 1 and if their income was between $400 and the next income level that they would be ranked 2?

And for a family of 3 if the income was between $0 and $428 they would be ranked 1 and between $429 and the next level they would be ranked 2?

If that is the case then let me know and I will try to come up with a solution for you. I will be out all morning, but I will work on it as soon as I can. I think Select Case is the way to go, something like this...

Select Case FamSize
Case 2
Select Case Income
Case 0 to 399
Textx = "Rank is 1"
Case 400 to 525
Textx = "Rank is 2"
Case....
End Select
Select Case FamSize
Case 3
Select Case Income
Case 0 to 428
...
End Select
End Select

I think you get the idea. Give it a go as you may have more time than I do at the moment. Let me know how you do...

Jack

 

[damsel]

Hi Jack

Thank you for being kind.
The case sounds marvelous.
I have up to 12 in FamSize.
and up to 66 in Ranks.
I'm out all day too. I'll give it a go when I'm back in the evening
(same time zone). Thank you again, no hurry Jack.

 

[WayneRyan]

Hi damsel & Jack,

Hate to butt in, but there are TOO many options to express
in IF or CASE statements ...

I think a table:

TablePriority:

FamilySize - Integer
Income - Currency
Rank - Integer

Then when given a family size and income she can use
one piece of code to find the ranking where the family
size is right and the income is the minimum of the ones
greater than entered.

hth,
Wayne