Premium Table

Z

zyus

How to incoporate premium table into my database so it can automate the
calculation of premium based on the age , term & loan amt.

The table will look like this in Excel

Age\Term 1 2 3 4 5
18 0.91 1.72 2.48 3.21 3.91
19 1.10 2.16 3.26 4.41 5.62
20 1.81 3.57 5.45 7.45 9.58

Thanks
 
T

Tom Wickerath

Hi Zyus,

At first glance, your data appears to represent a many-to-many (M:N)
relationship. These types of relationships require a minimum of three tables.
One of the tables is known as a join or linking table, and includes the
foreign key side of two one-to-many (1:M) relationships. In English, your
relationships appear to be:

1:M An age can include many terms and
1:M A term can apply to many ages

If this sounds correct, then you'll need one table of ages, one table of
terms, and a third join table to join the two. If all of this seems rather
confusing, then navigate to this link and read up on database design:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

How to incoporate premium table into my database so it can automate the
calculation of premium based on the age , term & loan amt.

The table will look like this in Excel

Age\Term 1 2 3 4 5
18 0.91 1.72 2.48 3.21 3.91
19 1.10 2.16 3.26 4.41 5.62
20 1.81 3.57 5.45 7.45 9.58

Thanks
 
J

John Vinson

How to incoporate premium table into my database so it can automate the
calculation of premium based on the age , term & loan amt.

The table will look like this in Excel

Age\Term 1 2 3 4 5
18 0.91 1.72 2.48 3.21 3.91
19 1.10 2.16 3.26 4.41 5.62
20 1.81 3.57 5.45 7.45 9.58

Thanks
Click to expand...

That's a good spreadsheet... but it's a poor design for a Table.

In a relational database, "fields are expensive, records are cheap".
Rather than a grid, you'll want a tall-thin table with one record for
each value. YOu don't say (and I'm not sufficiently familiar with
accounting to guess) what the 1, 2, 3 in the top row represents, but
your preferred table structure will have rows like

18; 1; 0.91
18; 2; 1.72
18; 3; 2.48
....
20; 4; 7.45
20; 5; 9.58


John W. Vinson[MVP]
 
J

John Vinson

john

wow that's a great saying.

im glad that i saw this.
Click to expand...

"Fields are expensive..."

yep, I love that. I need to run a Google Groups search and find out
who posted it first (if indeed it was on the newsgroups, it might have
originally been elsewhere).


John W. Vinson[MVP]
 
T

Tom Wickerath

Hi John,

I'm fairly certain that I have heard Michael Hernandez use that term more
than once during presentations he's made to the PNWADG (Pacific Northwest
Access Developer's Group). A quick Google search shows that lots of people,
including you, have posted this phrase (or variations of, such as "Records
are cheap; Fields are expensive) many times before. I know I've posted it
many times.

I'm willing to bet that if you have an electronic version of Database Design
for Mere Mortals available (I don't think I do) that you might find this
phrase published there.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

"Fields are expensive..."

yep, I love that. I need to run a Google Groups search and find out
who posted it first (if indeed it was on the newsgroups, it might have
originally been elsewhere).


John W. Vinson[MVP]
 
Z

zyus

The 1,2,3 represent the number of years...

If my customer want to apply for 10000 loan for 5 years and assuming his/her
age is 19. Manually i will calculate the insurance premium 10000/1000 * 5.62
= 56.20

Is there any way that can simplify this calculation.

Thanks
 
J

John Vinson

The 1,2,3 represent the number of years...

If my customer want to apply for 10000 loan for 5 years and assuming his/her
age is 19. Manually i will calculate the insurance premium 10000/1000 * 5.62
= 56.20

Is there any way that can simplify this calculation.
Click to expand...


Yes... follow the suggestion I made earlier, and use the age and the
number of years as criteria in a Query to look up the value.

John W. Vinson[MVP]
 
Z

zyus

Thanks

John Vinson said:
Yes... follow the suggestion I made earlier, and use the age and the
number of years as criteria in a Query to look up the value.

John W. Vinson[MVP]
Click to expand...
 
T

Tom Wickerath

If you want to create a query that gives you the look and feel of your
original spreadsheet, then create what is known as a Crosstab Query. Although
this type of query produces a read-only recordset, you can produce something
that will give you age values in one column, term values in other columns,
and the corresponding values.

Here is a tutorial I wrote for creating a crosstab query. My tutorial
involves several tables, but you could base the same type of query on a
single "tall-thin" table, as John described for you.

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

The 1,2,3 represent the number of years...

If my customer want to apply for 10000 loan for 5 years and assuming his/her
age is 19. Manually i will calculate the insurance premium 10000/1000 * 5.62
= 56.20

Is there any way that can simplify this calculation.
Click to expand...


Yes... follow the suggestion I made earlier, and use the age and the
number of years as criteria in a Query to look up the value.

John W. Vinson[MVP]
 
A

aaron.kempf

i can't wait for november 7th

i mean.. the pivot keyword in SQL 2005.. god i have waited a long time
for that lol
 
Top