Mike375

[Mile-O]

Now I am quite happy to make a rate calculator for each insurance company using one table IF anyone comes with suggestions. But people don't do that. They just keep repeating "you must normalise"

You aren't going to get other suggestions because the people who are suggesting this are the ones who know best. They understand why we have to normalise our data, how we should go about it, and - as the principle applies to every database - know how to offer advice on others' databases too.

You won't, however, hear them say that a problem can be resolved without a need to normalise as they know (even if it doesn't cause problems now) it may (and most likely will) cause problems going forward. Thus, to try and help anyone who doesn't want to normalise would be to go against their standard code of practice; to go against their own "ethics" of database design.

It's never going to happen. Personally, I'd rather not help anyone with a shoddy database that is unwilling to see the light than plough through their dodgy database offering ephemeral solutions.

I'm sure everyone who shouts and screams normalise feels the same.


With you Mike375, you claim in this situation [quoted above] that you would be willing to change to a one table solution (it may take more depending on normalisation) but it doesn't just work on a procedural level (i.e. this bit can be normalised but this bit stays as it is); the whole thing has to be normalised.

 

[Kodo]

Hey Kodo , maybe you should try to teach Mike regular expressions? Now that would be an interesting thread .....

I'd rather subject my nipples to hot tar..

 

[Rich]

I'd rather subject my nipples to hot tar..

that's as perverted as having 2000 Macros

 

[Uncle Gizmo]

I'd rather subject my nipples to hot tar..

Spelling mistake?

I'd rather subject my nipples to a hot tart....


Why do I keep thinking about a hot, ----------- dripping, ----------- candle?

 

[Mike375]

Mile-O-Phile

Anyone who makes these rate calculators would all prefer to run them from a single table if they could overcome the rate changes from a company.

Is there an easy way that you can deal with this situation bearing in mind that we want to put their rates straight into the system. Typing out the rates is just not an option as there are far too many of them.

Company A supplies base rates in the following format:

age........
30..........Male Non Smoker........rate.......rate......rate...and on
30..........Male Smoker..............rate.......rate......rate...and on
30..........Female Non Smoker.....rate.......rate......rate...and on
30..........Female Smoker...........rate.......rate......rate...and on


Company B

age....
30........Male Non S........Male S.......Female Non S.....Female S

I currently do the rates with DLookup for a SetValue expression and which action line runs depends on what was selected on the form such as age, ocupation class, aiting period, benefit period, policy type and so on. Then the rest of the macro does the discounts for amount of benefit and then the mode of payment conversion.

Mike

 

[Pat Hartman]

Is there an easy way

- NO - the solution takes some analysis. I have solved your exact problem. Except that instead of insurance rates it was common carrier rates. We examined rate schedules from dozens of carriers from FedEx, USPS, and DHL to Joe's courier service. The big guys had complex rate structures that were all different, although available in machine readable format, and the little guys simply had strange rules. With proper analysis, you can make sense out of complex data structures. The solution lies in finding the common thread and building on that. More than one table was required but that was because the rate schedules were hierarchial in nature not because we needed a separate table for each carrier.

We even provided the facility for our customers to add their own carrier information and rate schedules manually since the product was actually sold to customers all over the country. Customers who ship thousands of packages each day use this software to calculate postage rates and determine the cheapest/fastest/whatever carrier to minimize their shipping costs while maintaining customer satisfaction. We provided data and updates for the major carriers but there was no way that we were going to provide carrier rate schedules for every single common carrier doing business within or from the US.

Your problem is that the schedules sent to you by each insurer look "normal" because you are used to looking at unnormalized data. I'm guessing that each individual schedule has 2 or more dimensions and if you normalized each schedule individually, you would see how they could have all been stored in the same set of hierarchial tables.

What you fail to realize is that in the carrier rate system and in your insurance quote system, you have two choices
1. Normalize the data on the way in
2. Modify your application every time you get a new provider

We chose to normalize the data on the way in. That is why our customers can add their own carrier information and the application doesn't need to be modified if we need to add new carriers. This is a commercial grade application and you would recognize my client's name even down under.

 

[Mike375]

2. Modify your application every time you get a new provider

Pat,

Yes, that is correct. I will probably be adding one in a month or so and I basically make copies of an existing rate calculator and then make changes to the field name references etc. as well as the various "conditions" for apply a rate formula.

I'm guessing that each individual schedule has 2 or more dimensions and if you normalized each schedule individually, you would see how they could have all been stored in the same set of hierarchial tables.

I am not sure what you mean by 2 or more dimensions. Is one dimension fields across the page and a second dimension is rows. If so, then that is the case for the base rates. There is usually a minimum of two tables with one table being for Disability or Income Replacement insurance and the other for term life, total and permanent disability and trauma insurance. There are then other tables for occupation classifications and the various premimium loadings for the different occupations as well as those for sports and pastimes. There is also usually a table for the various medical and financial requirements that apply to the various levels of benefits.

The rate tables typically have a number of records from either 21 to age 60 and one record for each age or they are like the illustration I did where there will be several records for each age.

They are a fairly big deal to make as businesses in Australia that do all the rates for the different companies employ several computer people and that is all they do. I don't do all the rates, only those for the policies our business is most likely to be writing. For the odd occassion when something comes that I do not do then we just use the insurance company's quote system. To all of them I would have to move from the insurance business to the computer business

Mike

 

[Kodo]

that's as perverted as having 2000 Macros

yes, but I'm well aware that the tar is, infact, hot and can cause damage to my skin such that it may require grafting.

 

[Pat Hartman]

I rest my case.

I (and I'm sure that I speak for all of us) don't give a flying f___ how you cope with your poor design. You clearly got no benefit from my last post.

I am current reading a book that referred to a condition described by Thomas Aquinas. He described it as Ignorantia Affectata. Sounds to me like what you're afflicted with.

 

[bjackson]

Mike375
I tried to be polite in the my last post with you,but fair dinkum, you will give us Aussies a bad name.Instead of taking up the time of the many experts(And they are experts) in this forum,why not bundle up your program and make a fortune by selling such an excellent product worldwide.You could probably make enough money to then sack everbody here who donates their time and expertise trying to help people.They help im sure,because it gives them a buzz knowing they have helped,but it must give them the S**** being constanly argued with about F***all.

Bjackson

 

[Mrs.Meeker]

Reading this has made me realize that I have not sufficiently thanked ALL OF YOU who have helped me. When I first started learning Access (and I know I still have a loooonnnngggg way to go, Pat Hartman told me to read about normalization. She said I was treating Access like Excel and SHE WAS SO RIGHT! Being a big Excel user for years caused a slight brain blockage (that and my left handed affliction - supposed to make me creative - seems to make me slow

Anyway, I'm glad I'm not a hardheaded old coot who doesn't want to listen, but argue and look like an idiot.

I'm glad you are all out there helping people like me who want to learn and I'm sorry that there are others who can be so unpleasant. Of course you gotta LAUGH!!!

Thanks again ya'll.
Yeah, I lived in Texas for a spell!
Rhonda

 

[KenHigg]

Ms. Hartman

 

[Mrs.Meeker]

Blush

I was just informed of my error in another post. When I embarrass myself I do it up right!

sorry again!