R
Rich
Guest
Why are you bothering to even ask? stop trying to prove a point!
M
Mike375
Guest
Rich said:don't bet on it
Dear Rich,
If you think I am a fool then so be it.
Mike
Brianwarnock
Retired
- Local time
- Today, 13:41
- Joined
- Jun 2, 2003
- Messages
- 12,667
Here we go again.
Can we not start another thread titled "Mikes database" and leave the rest of the forum in peace. If this "virus" spreads the forum won't be worth visiting.
Brian
Can we not start another thread titled "Mikes database" and leave the rest of the forum in peace. If this "virus" spreads the forum won't be worth visiting.
Brian
Mile-O
Back once again...
- Local time
- Today, 13:41
- Joined
- Dec 10, 2002
- Messages
- 11,305
Mike375 said:
One word: normalisation.
One word: normalisation.
They are.
No, normalise.
No. That's a many-to-many relationship. You need a junction table which, by the sounds of it, you obviously don't have.
I don't do blank fields.
You only need one table for rates.RateID
Rate
CompanyID
DateEnds (Default Value: #31/12/2200#)
Now, when a company's base rate changes you just update the DateEnds field to the date it ends and append the new rate and the CompanyID.
Are you suggesting that with the different life companies we just leave a bunch of blank fields because Company A has products ABC and Company B has ZYX. I guess it would be OK since they share several products that are essentially the same.
Once again, you need a junction table.
I'd post an example but I can't convert back to A95 for you.
R
Rich
Guest
yet again our friend from down under has turned a valid thread into a pissing contest, with questions posed as a challenge. Do you think it's time to lock it and re-post Paul's original question so that sensible posts can be added? should we have a poll on it ?
Brianwarnock
Retired
- Local time
- Today, 13:41
- Joined
- Jun 2, 2003
- Messages
- 12,667
Its not really Paul's original question that needs answering but that posed by his 2nd and subsequent posts, but yes to your question.
Brian
Brian
M
Mike375
Guest
Mile,
From your posting:
RateID
Rate
CompanyID
DateEnds (Default Value: #31/12/2200#)
Now, when a company's base rate changes you just update the DateEnds field to the date it ends and append the new rate and the CompanyID.
If only it was that simple.
I won't bore you with all the details. However, keep in mind that there are computer businesses in Australia whose whole business revolves around doing rate calculators for different life companies. I can absolutely assure you that one table does not hold the rates.
and append the new rate Sounds good
Those of use who do our own rate calulators (and there are not many of us!!) get the rates from the insurance companies on either Excel or Access tables. None of them are compatable. Apart from the product differences, the different insurance companies set the rates out differently. For example Company A might go across a row for each age for every policy type for smoker, non smoker, male and female. Another company will have smoker, non smoker, male and female all in one column. Another company will have a completely separate set of rates for each policy type for smoker etc.
The other issue is that Company A and Company B both have rates for products (and ancilliary benefits) that the other company does not have.
The rates also vary in terms of "$ per month for $x of benefit" and "$ per year for $x of benefit"
Another variation you get is that one company will have a raw "rate" for smoker, female etc and another company uses the same raw rate for all but factors are applied to the rate if the person is a smoker etc.
My data base get a bit complicated because we tie the rate calculators and product comparisons to the telemarketing.
If a data base is like a house then I honestly believe if you were out here and had a free hand for one year, then after you were finished this "house" would still basically have the same number of rooms in the same position. Of course your doors and windows would work better than mine etc but the overall I think the basics would be similar. Where I might need 4 calculated fields in a query feeding off each other to get the result, you will be able to do it in one field.....or perhaps some other way.
To give you an idea of how "wide" a table would be (or how "deep") here are the basic policy benefits for Income replacement insurance.
Benefit Periods
2 years Sickness 2 years Accident
2 years Sickness 5 years Accident
5 years Sickness 5 years Accident
2 years Sickness Accident to age 65
5 years Sickness Accident to age 65
Sickness and Accident to age 60
Sickness and Accident to age 65
Lifetime Sickness and Accident.
Waiting Periods
2 wks
4 wks
8 wks
13 wks
26 wks
52 wks
The comes the premium rate for the various ancillary benefits which vary depending on which Benefy Period policy they are attached to.
Then all of the above is doubled for Level Premium. That is, there are two completely separate rates for Stepped and Level premium.
The in most cases the insurance company has different raw rates for differnt occupation classes for Income Replacement. There is on average about 5 occupation classes.
Then add to the above the companies that use different "raw' rates for smoker and gender.
Now add to the above all the variations on Life cover, Trauma insurance and TPD and the separate rates that apply when these policy benefits are joined.
And lastly, some insurance companies tend to "going down the page" and others tend to "going across the page". If you can stick all of that from a dozen different insurance companies in one table then that would be something to behold.
Regards and take care
Mike
From your posting:
RateID
Rate
CompanyID
DateEnds (Default Value: #31/12/2200#)
Now, when a company's base rate changes you just update the DateEnds field to the date it ends and append the new rate and the CompanyID.
If only it was that simple.
I won't bore you with all the details. However, keep in mind that there are computer businesses in Australia whose whole business revolves around doing rate calculators for different life companies. I can absolutely assure you that one table does not hold the rates.
and append the new rate Sounds good
Those of use who do our own rate calulators (and there are not many of us!!) get the rates from the insurance companies on either Excel or Access tables. None of them are compatable. Apart from the product differences, the different insurance companies set the rates out differently. For example Company A might go across a row for each age for every policy type for smoker, non smoker, male and female. Another company will have smoker, non smoker, male and female all in one column. Another company will have a completely separate set of rates for each policy type for smoker etc.
The other issue is that Company A and Company B both have rates for products (and ancilliary benefits) that the other company does not have.
The rates also vary in terms of "$ per month for $x of benefit" and "$ per year for $x of benefit"
Another variation you get is that one company will have a raw "rate" for smoker, female etc and another company uses the same raw rate for all but factors are applied to the rate if the person is a smoker etc.
My data base get a bit complicated because we tie the rate calculators and product comparisons to the telemarketing.
If a data base is like a house then I honestly believe if you were out here and had a free hand for one year, then after you were finished this "house" would still basically have the same number of rooms in the same position. Of course your doors and windows would work better than mine etc but the overall I think the basics would be similar. Where I might need 4 calculated fields in a query feeding off each other to get the result, you will be able to do it in one field.....or perhaps some other way.
To give you an idea of how "wide" a table would be (or how "deep") here are the basic policy benefits for Income replacement insurance.
Benefit Periods
2 years Sickness 2 years Accident
2 years Sickness 5 years Accident
5 years Sickness 5 years Accident
2 years Sickness Accident to age 65
5 years Sickness Accident to age 65
Sickness and Accident to age 60
Sickness and Accident to age 65
Lifetime Sickness and Accident.
Waiting Periods
2 wks
4 wks
8 wks
13 wks
26 wks
52 wks
The comes the premium rate for the various ancillary benefits which vary depending on which Benefy Period policy they are attached to.
Then all of the above is doubled for Level Premium. That is, there are two completely separate rates for Stepped and Level premium.
The in most cases the insurance company has different raw rates for differnt occupation classes for Income Replacement. There is on average about 5 occupation classes.
Then add to the above the companies that use different "raw' rates for smoker and gender.
Now add to the above all the variations on Life cover, Trauma insurance and TPD and the separate rates that apply when these policy benefits are joined.
And lastly, some insurance companies tend to "going down the page" and others tend to "going across the page". If you can stick all of that from a dozen different insurance companies in one table then that would be something to behold.
Regards and take care
Mike
Pauldohert
Something in here
- Local time
- Today, 05:41
- Joined
- Apr 6, 2004
- Messages
- 2,097
How come my question can get 47 answers here and only 6 on the question. I am Joking.
Similar threads
