434,587 Members | 1,101 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

# Lookup table

 P: 44 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:coverageamt 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! May 1 '08 #1
11 Replies

 Expert 2.5K+ P: 3,072 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:coverageamt 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) May 1 '08 #2

 P: 44 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. May 1 '08 #3

 Expert 2.5K+ P: 3,072 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) May 1 '08 #4

 P: 44 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? May 2 '08 #5

 Expert 2.5K+ P: 3,072 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: Expand|Select|Wrap|Line Numbers =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) May 2 '08 #6

 P: 44 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.....:) May 2 '08 #7

 Expert 2.5K+ P: 3,072 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) May 2 '08 #8

 P: 44 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! May 2 '08 #9

 Expert 2.5K+ P: 3,072 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) May 2 '08 #10

 P: 44 InsChartID-- --AgeRange --MinAge--- MaxAge --------VLIFE ---LifeMonthlyPrice -----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? May 2 '08 #11

 Expert 2.5K+ P: 3,072 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: Expand|Select|Wrap|Line Numbers (TableA.Age between TableB.AgeLower and TableB.AgeUpper and TableA.Amount between TableB.VLIFEmin and TableB.VLIFEmax)   Getting the idea ? Nic;o) May 2 '08 #12