Lookup table

[Zulema]

I have a chart with information on premiums charged to an employee based on the employees age and amount of coverage selected. I have a made a table based on this information. My table includes the following fields:

Table: tblExlife
field1:Id
field2:agerange
field3:coverage amt
field4:premium

Can i build a query that will return the correct premium depending on the age and coverage amount selected by each employee? i was thinking of making this work by way of ranges. I am not sure if i am headed in the right direction with this...can someone please point the way!

[nico5038]

I have a chart with information on premiums charged to an employee based on the employees age and amount of coverage selected. I have a made a table based on this information. My table includes the following fields:

Table: tblExlife
field1:Id
field2:agerange
field3:coverage amt
field4:premium

Can i build a query that will return the correct premium depending on the age and coverage amount selected by each employee? i was thinking of making this work by way of ranges. I am not sure if i am headed in the right direction with this...can someone please point the way!

Basically you'll need to store the lower and upper bound of the age range lke:
ID
AgeLower
AgeUpper
Coverage

Now you'll be able to JOIN tables by using in the JOIN instead of a:
TableA.Age = TableB.Age
the expresion:
TableA.Age between TableB.AgeLower and TableB.AgeUpper

Getting the idea ?

Nic;o)

[Zulema]

Basically you'll need to store the lower and upper bound of the age range lke:
ID
AgeLower
AgeUpper
Coverage

Now you'll be able to JOIN tables by using in the JOIN instead of a:
TableA.Age = TableB.Age
the expresion:
TableA.Age between TableB.AgeLower and TableB.AgeUpper

Getting the idea ?

Nic;o)

ok i think i get the picture....but the the premium will be based on ranges of: under 30; 30 to 34; 35 to 39; etc...and coverage amounts of 10000; 20000; 30000 and so on. That means that a 31 yr old will have a premium of say .90 if he goes with 10000 or 1.80 if he chooses 20000 etc.. . In this age range the premium is an increase of .90 for each 10,000 increase in coverage. i hope i am making sense when explaining it....

I am not sure how structure my table in order to make it work with the different coverage range amounts also.

[nico5038]

ok i think i get the picture....but the the premium will be based on ranges of: under 30; 30 to 34; 35 to 39; etc...and coverage amounts of 10000; 20000; 30000 and so on. That means that a 31 yr old will have a premium of say .90 if he goes with 10000 or 1.80 if he chooses 20000 etc.. . In this age range the premium is an increase of .90 for each 10,000 increase in coverage. i hope i am making sense when explaining it....

I am not sure how structure my table in order to make it work with the different coverage range amounts also.

Looks like you need an additional piece of information. You stated "if he chooses 20000", this value needs to be "known" e.g. in your person's table like "coverage category" and can be added to the JOIN expression I gave you in my previous post.

Just make sure all ranges are "covered" like Ages from 0 - 999 and coverages from 0 to ???

Nic;o)

[Zulema]

I now have a table that looks like this:
ID-----AgeRng-----MinAge-----MaxAge-----CvgAmt-----Premium
1------under 30-------0---------------29------------10,000-------.90------
2------30 - 34---------30--------------34------------10,000-------.90------
3------35 - 36---------35--------------39------------10,000-------1.30----

Is this what it needs to look like?

[nico5038]

I now have a table that looks like this:
ID-----AgeRng-----MinAge-----MaxAge-----CvgAmt-----Premium
1------under 30-------0---------------29------------10,000-------.90------
2------30 - 34---------30--------------34------------10,000-------.90------
3------35 - 36---------35--------------39------------10,000-------1.30----

Is this what it needs to look like?

The AgeRng is more or less obsolete when you can use the MinAge and MaxAge as a reference on reports by using:

=Me.MinAge & "-" & Me.MaxAge

(BTW: the 3rd ID is not "consistent " :-)

Still not sure where the amount that needs to be compared with CvgAmt comes from...

Nic;o)

[Zulema]

i think i am confusing you and i know i am confused. i will try to explain: the table i just listed, actually looks like the chart i use to lookup and find out what premiums need to be charged to an employee based on age and coverage amount criteria. my train of thought is to match the amount of coverage , and age of the employee field which i have on the Employeetbl to the Rangetble i just listed. Do i make sense? I might be way off. Please be lenient.....:)

[nico5038]

No problem Zulema, when you have just one CvgAmt to deal with, then the table is perfect.
However when there's a "staging" for the CvgAmt per Age range, then an additional field with the max value (or also a range) of the CvgAmt will be needed, or all rows with a Max of the CvgAmt will have to be processed.
Like:
Age 0-25 10,000
Age 0-25 20,000
etc.
That applies to employees earning between 5,000 and 25,000.

Nic;o)

[Zulema]

No problem Zulema, when you have just one CvgAmt to deal with, then the table is perfect.
However when there's a "staging" for the CvgAmt per Age range, then an additional field with the max value (or also a range) of the CvgAmt will be needed, or all rows with a Max of the CvgAmt will have to be processed.
Like:
Age 0-25 10,000
Age 0-25 20,000
etc.
That applies to employees earning between 5,000 and 25,000.

Nic;o)

Ok, i have all the rows on the table, information on my table lists coverage up to 100,000 with corresponding premiums. What is next? Thanks for being sooo patient!

[nico5038]

Ok, i have all the rows on the table, information on my table lists coverage up to 100,000 with corresponding premiums. What is next? Thanks for being sooo patient!

So how's your table looking now ?

Nic;o)

[Zulema]

InsChartID-- --AgeRange --MinAge--- MaxAge --------VLIFE ---LifeMonthlyPric e
-----1---------under 30-----------0------------29-------$10,000.00-----------$0.90----------
2 30 - 34 30 34 $10,000.00 $0.90
3 35 - 39 35 39 $10,000.00 $1.30
4 40 - 44 40 44 $10,000.00 $2.20
5 45 - 49 45 49 $10,000.00 $3.10
6 50 - 54 50 54 $10,000.00 $5.30
7 55 - 59 55 59 $10,000.00 $9.10
8 60 - 64 60 64 $10,000.00 $14.30
9 65 - 69 65 69 $10,000.00 $22.50
10 70 - 74 70 74 $10,000.00 $36.00
11 75 and over 75 100 $10,000.00 $63.80
12 under 30 0 29 $20,000.00 $1.80
13 30 - 34 30 34 $20,000.00 $1.80
14 35 - 39 35 39 $20,000.00 $2.60

Pretty long! Does this look ok?

[nico5038]

Yes, looks OK, but to JOIN in a query you'll be better off using also a range for the VLIFE field like "VLIFEmin" and "VLIFEmax".

Thus You can connect the Person with both the "Age-range-JOIN" and a "VLIFE-range-JOIN".
So in the SQL INNER-JOIN code you can use:

(TableA.Age between TableB.AgeLower and TableB.AgeUpper
and
TableA.Amount between TableB.VLIFEmin and TableB.VLIFEmax)

Getting the idea ?

Nic;o)