查找表 [英] Lookup table
问题描述
我有一张图表，其中包含根据员工年龄和所选保险金额向员工收取的保费信息。我根据这些信息制作了一张桌子。我的表包括以下字段：
表：tblExlife
field1：Id
field2：agerange
field3：coverageamt
field4：premium
我可以构建一个查询，根据每位员工选择的年龄和承保金额，返回正确的保费？我想通过范围来做这项工作。我不确定我是否朝着正确的方向前进......有人可以指出方向！
我有一张图表，其中包含根据员工年龄和所选保险金额向员工收取的保费信息。我根据这些信息制作了一张桌子。我的表包括以下字段：
表：tblExlife
field1：Id
field2：agerange
field3：coverageamt
field4：premium
我可以构建一个查询，根据每位员工选择的年龄和承保金额，返回正确的保费？我想通过范围来做这项工作。我不确定我是否朝着正确的方向前进...有人可以指出方向！
基本上你需要存储年龄范围的下限和上限lke：
ID
AgeLower
AgeUpper
覆盖范围
现在你可以通过JOIN而不是JOIN来加入表格了a：
TableA.Age = TableB.Age
表达式：
TableA.AgeLower和TableB.AgeUpper之间的TableA.Age >
获得想法？
Nic; o）
基本上你需要存储年龄范围的下限和上限lke：
ID
AgeLower
AgeUpper
覆盖范围
现在你可以通过在JOIN中使用而不是a来加入表格：
TableA.Age = TableB.Age
expresion：
TableA.AgeLower和TableB.AgeUpper之间的TableA.Age
得到这个想法？
Nic; o）
好吧我想我得到的照片......但是保费将基于以下范围：30岁以下; 30至34; 35至39;等......并且覆盖金额为10000; 20000; 30000等。这意味着一个31岁的人如果选择20000等等，如果他选择10000或1.80，他们将获得0.90的溢价。在这个年龄段，每增加10,000个保险，保险费增加0.90。我希望我在解释它时有意义....
我不确定我的表格结构如何使其适用于不同的覆盖范围数量。
ok我想我得到的照片......但是溢价将基于以下范围：30以下; 30至34; 35至39;等......并且覆盖金额为10000; 20000; 30000等。这意味着一个31岁的人如果选择20000等等，如果他选择10000或1.80，他们将获得0.90的溢价。在这个年龄段，每增加10,000个保险，保险费增加0.90。我希望我在解释它时有意义....
我不知道我的表格结构如何使其适用于不同的覆盖范围数量。
看起来您需要额外的信息。您说如果他选择20000，则该值需要已知。例如在你的人的表格中，如覆盖类别并且可以添加到我在之前的帖子中给你的JOIN表达式。
只要确保所有范围都被覆盖即可。像年龄从0到999，覆盖范围从0到???
Nic; o）
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!
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)
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.
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)
这篇关于查找表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
