Not sure where to start 1

[ba4crm]

Hi all,
I was hoping I could get some direction for you folks on how to tackle this problem.
Background:
A person applying for health coverage gets assigned certain number of points which range from 1 - around 200. Based on the points and their age group, we decide what level of coverage (5 levels) they get. Each level has a maximum point allowed. The number of points per plan and level is very random. The only sequence is that Level 1 is the lowest number of points, level 5 the highest. If you exceed the amount for one level, you get the next level.
A 4 year old with 11 points, may get level 3 in Plan A, but level 2 in Plan B.
A 40 year old with 11 points will get level 2 in Plan A and level 1 in Plan B.
There are 4 different age groups, 5 different health plans, and 5 different levels of coverage.
What is needed:
If a user enters a person's age and how many points was assigned to them, I need to provide the user with all plan names and levels that the person would qualify for.
What is the most efficient way of doing this?

Thank you for your time

 

[Golom]

[tt]tblPlans
PlanID PlanLevel Age Points
A 1 0 5
A 1 1 5
: :
X 1 99 199
X 5 99 200
[/tt]
Then

Select PlanID, MAX(PlanLevel) As [Level]

From tblPlans 

Where Age = [Enter Age]
  AND Points >= [Enter Points]

Group By PlanID

 

[SgtJarrow]

Since you in the VBA coding area, I am assuming you are looking for a function to help calculate this. If that is not the case let me know.

Something like:

' Assumption:  Only 1 Level Per Plan Group Allowed

Public Function FindPlans(intAge As Integer, intPoint As Integer) As String

    Dim strPlans As String

    Select Case intAge
        Case 0 To 12
            ' Plan A
            Select Case intPoints
                Case 1 to 25: strPlans = "Plan A Level 1"
                Case 26 to 50: strPlans = Plan A Level 2"
            End Select
            ' Plan B
            Select Case intPoints
                Case 1 to 12: strPlans = "Plan B Level 1"
                Case 13 to 50: strPlans = Plan B Level 2"
            End Select
            ' Continue for all plans
        Case 13 to 43
            ' Plan A
            Select Case intPoints
                Case 1 to 25: strPlans = "Plan A Level 1"
                Case 26 to 50: strPlans = Plan A Level 2"
            End Select
            ' Plan B
            Select Case intPoints
                Case 1 to 12: strPlans = "Plan B Level 1"
                Case 13 to 50: strPlans = Plan B Level 2"
            End Select
            ' Continue for all plans
        ' Continue for all age groups
    End Select

End Function

Quick and dirty but should work. It could be cleaned up to shrink the code a bit but this is off the top of my head at the moment...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Remou]

I think it will be possible to use DLookUp, if you have a High and Low on the points and age.

DlookUp("Plan","tblPlanA", "(" & [Age] & " Between LowAge And HighAge) And (" & [Points] & " Between LowPoints And HighPoints)")

Or there abouts.

 

[ba4crm]

Thank you all!
Golom - I am not as familiar with SQL code as I should be, but I think I follow the logic and can do that in a query form. I do have one question though - because the age is a range of ages (0 -18; 19 - 39 etc) instead of a fixed number, would I change the code to read "WHERE Age >= [Enter Age]" ?

mstrmage1768
I have seen Case statements being used before so have a general idea of how it works. I will read some more on it and see if I can figure it out. I entered the question in the VBA Coding area because I didn't think this would have been possible without using a function.

Remou
I have used Dlookup before (in excel) and I think I can take your suggestion and make it work too. I understand Dlookup enough to understand the logic!

All - thank you so much! You folks are AWESOME!!

 

[Golom]

[tt]tblPlans
PlanID PlanLevel MinAge MaxAge Points
A 1 0 18 5
A 2 0 18 7
: :
X 1 90 99 190
X 5 90 99 200
[/tt]

Select PlanID, MAX(PlanLevel) As [Level]

From tblPlans 

Where [Enter Age] BETWEEN MinAge AND MaxAge
  AND Points >= [Enter Points]

Group By PlanID