Mike375

[KenHigg]

Mike,

In response to your post where you suggested the user make a separate table for each customer: Can you fathom a db where you never have to worry about creating new tables, etc. Where all you do is enter data on forms. That is the essence of the db's that the senior posters in this forum strive to create. And the reason is so that an end user does not have to have a clue as to what a query or macro is. They simply put in data, push a button, and presto, their data comes out and then they can focus on running their business and not having to read Access instruction manuals. They say "Give me a form to do my inputs and button to get my reports - I do not even care that you may be using Access to create it!"

Now that is not to say that a db where you continuously add tables, etc., through out the life of the db is not a useful tool. You have proven other wise. It's just that some of the solutions you propose can lead to a situation where the end user will have to be an Access person to use. See the problem?

Eventually, a db like the one you have gets to the point where redesigning it is hopeless but your business can not run with out it. Now, everything is still cool because you're there to baby it, but I would suspect that when you go to show your potential new business owner how to run it, you're going to be hosed and it will be a liability that few new potential business owners will want to deal with...

Having said all that - can you see why the senior posters here sometimes get upset with your advice?

Can we still be friends?
Ken

 

[Mike375]

Ken,

All data here is either done via forms or it is entered in bulk with names we buy. The names, addresses etc that are entered in bulk go into a holding table and a "click" on a label brings them into the data base as required. That same "click" also moves names etc out of the main tables to "reject tables".

When records are entered in limited numbers a "click" is done for "new record" and a form opens. This has the various checks for fields that must have entries etc. I might add that on March 11 this year gov't legislation imposed many requirements with the selling of insurance and investments. Two of the larger groups are copying what I have made and because "it is so easy to use".

But to return to bjacksons problem. When he posted there was no information to indicate how complex his problem might be or might not be but he did indicate that updating prices was his key issue. His ideal solution if he could do would be to get the price changes on disk from the suppliers. Those of us in Australia who make rate calculators for the insurance companies all do this. Each insurance company supplies its own "quote system" but that has obvious disadvantages if you want to tie together quotes from more than one insuance company. If you tried to make one table to cover all companies you would be there forever. What is much simpler is to have duplicate systems that join at the end.

But you play it as you see it and what is the best. In the case of commission accounts I approach that a different way. We import an Excel spreadsheet and a query on the table simply has new fields for names I want using the data from their fields. Obviously we need a query for each company as they all call things by different names. But in that case all commission accounts finish in the one table. I choose to do the commission accounts differently to the premium calculators and one reason is that the commission accounts are "dead" in the sense that nothing is done with them except for searching, filtering and so on. The commission data we get from the insurance companies is also a lot closer in layout than the premium rates. If you were to look at the premium rates as they come form the insurance companies it would be hard to recognise them as being from the same industry.

If bjackson has as many "rates" as he appears to have then I stick with what I first said and that is I would be looking to get the rates on disk off the suppliers. It will be in their interest to do that as his company is their customer. I did qualify what I said about doing a table for each supplier on the basis that his pricing structure was complex like the life company rates and that he could get such pricing from the supplier on disk.

He did mention that there are 2000 colours of which 500 might have the same price rate so that means there are at least 1500 different prices on colours. His description of the prices/colours did not sound as if it could be calculated from a few base prices. Now if his prices in that area are as it appears, does he sit down and type in 1500 prices when there is a price change? Hence I suggested looking to get the prices from the supplier. I bet they have them on a computer because he said they have printed sheets. Thus it is quite possible at the end of the day that he might be best served by using their raw data and if he has a table that is a match for Company A, Compnay B and so on then updating prices becomes very easy and very accurate.

Now let me throw something back to you You don't need to be on this forum for very long to see that very posters post more than a few times and a huge number of the posters don't come back to their threads. Also, the posting activity is very small in relation to the number of members. Could there be a reason? The gang of a few might like it that way, I don't know. When I posted a table's contents on that Normalisation/Denormalisation thread with the 50 or so fields I knew some would immediately jump in.....must be split into tables.....but I saw Pat Hartman was not that way and when I asked "split how" we were down to a couple of fields like postcode.

In my opinion the forum would be more productive if it was a discussion forum rather than the dogmatic approach of a few.

As to someone buying this business it will be a larger group and most likely a group owned by one of the life companies. I can tell you from experience that it would not matter how I made this data base they will remake it I sold part of it about 3 years ago to a life company and the main issue was what it did and the screen displays, no one could give a stuff of how it got there. You see, they have row upon row of people doing computers but the people are not on the street with selling insurance so some of the stuff they make is pretty useless.

Best regards,

Mike

 

[Rich]

It's time to vote off , the weakest link

 

[KenHigg]

I will assume that was a no to the question:

Can you fathom a db where you never have to worry about creating new tables, etc.?

ken

 

[Mike375]

Ken,

A data base that was static would not need new tables.

But with the premium calculators I don't see how you can have all companies in one table. In fact those people on the Denormalisation/Normalisation thread would all recommend a table for each company to avoid null fields. The reason being that the companies do not all have the same benefits available and so the field for Benefit XYX base premium will be blank for many companies.

And how would you deal this layout.

Company A supplies base rates in the following format

age........
30..........Male Non Smoker
30..........Male Smoker
30..........Female Non Smoker
30..........Female Smoker

Company B

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

Some companies use "age nearest birthday" and others use "age next birthday". They also express the premium rate differently. Some will be so many dollars per month per $10000 and some will be so many dollars per year per $1000. And you would want to put all the companies in one table.

Of course if you entered the 1000s of rates yourself for each company then that would be OK to use one table.

So, if I decide to add another insurance company to the rate calculator then another table will come on board.

Mike

 

[KenHigg]

Mike, There is no way I would even think about getting into a discussion about insurance, premium calculators, etc., with you (or anyone) and attempt to give advice about how to model a db (set the tables up ), without documenting a comprehensive set of business rules. It would be (is), a futile series of yeah - buts.

I can assure you, that if you can document the requirements and order them in a logical manner, you can build a db that will not need a new table every time a new customer comes on board. You may need to script some new functions, but new tables - not.

You are obviously a pretty good insurance man and know the racket pretty good. Would your time and energy be better spent in your senior years doing what you do best and not spent bickering about db design on this forum?

I know this sounds bitter but you do seem to put a lot of energy into a pointless battle.

Can we still be friends?
ken

 

[Mike375]

Ken,

I enjoy being a thorn and watching peope like Rich following my every move.

I can assure you, that if you can document the requirements and order them in a logical manner, you can build a db that will not need a new table every time a new customer comes on board. You may need to script some new functions, but new tables - not.

I have no doubt of that but at the end of the day one must do what is best for your business. I can assure you that if you did the premium rate calculators you would rapidly come to the conclusion that using the raw data as it comes from the insurance company is the easiest way. It simply means that rate changes are just a case of replacing the contents of the table with their new rates.

Macro actions

Open Delete Query
Open Append Query


Go to rate calculator form and Presto, we are in business.

In addition because I use their raw data as given to me there can be never any problems down the track with quote errors.

Mike

 

[Rich]

Ken,

In fact those people on the Denormalisation/Normalisation thread would all recommend a table for each company to avoid null fields.Mike

No they wouldn't

 

[Mike375]

From Kevin S

as the data becomes available it is added to the database without having to store blank/null data in the first place.

If you have one table for all companies you will have heaps of records that are null in some fields. Not that either myself or others who make rate calculators use a table per company to avoid empty fields.

 

[KenHigg]

I enjoy being a thorn

Hum...

Then I quess there is nothing left for me to say, Sorry I wasted your time...

ken

 

[Mike375]

Hum...

Then I quess there is nothing left for me to say, Sorry I wasted your time...

ken

Look at the icon that was used.

 

[Rich]

 

[Kevin_S]

From Kevin S

as the data becomes available it is added to the database without having to store blank/null data in the first place.

If you have one table for all companies you will have heaps of records that are null in some fields. Not that either myself or others who make rate calculators use a table per company to avoid empty fields.

And I promised myself I would not respond to any of your posts and now look what happens... I guess when your misquoted you have to stand up...

You, again, have misunderstood a fairly common principle in db design and my comment in NO WAY meant to create a unique table for each customer. What I said was that, fundamentally, you would have a table (tblCustomer) where you would have a cumstomerID and other related customer information. Then you have related tables (many to one, many to many, one to one relationships with tblCustomer) where you hold data related to that customerID. This setup would eliminate null values because you would not add attribute information to these tables until it is available, thus, eliminating the storing of nulls. Sorry Mike but this is pretty basic stuff and many of the senior posters on this forum tried repeatedly during your first few posts to this forum to help you understand and apply these concepts to your application - not to spite you but to assist you and you have repeatedly told them how wrong they are. Furthermore, you now tell others seeking help ways to do things that are not correct - thus members get even more frustrated... Im sure if you spent as much energy reading on data normalization as you did posting to this forum to defend your designs you'd probably be a master in this area by now... Also, I seriously suggest opening up the Northwind database example that comes with Access and looking at the db scema as these same principles are applied there as well(if you do not have Northwind db you can download it free from Microsoft at their website)

Feel free to now post away about how this is preposterous and how it doesn't work in your case- I wont respond so attack at will...

Good Luck,
Kevin

 

[Kodo]

I think I've lost 10 IQ points for reading this thread..

 

[ColinEssex]

I think I've lost 10 IQ points for reading this thread..

I've lost the will to live. . . . . . . . . . yawn

Col

 

[Mike375]

Kevin

I am familiar with the Northwinds db. My own db is full of One to Many.

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"

Now perhaps things are different in Australia but I know several people who make pricing/rate calculators for different suppliers and they use a different table for each supplier for ease of updating the prices.

I might have all this wrong but it seems to me that to get the different insurance companies base rates into one table then a lot of formatting changes will have to be done since they present the rates so differently to each other.

As poste above and a very simple example:

Company A supplies base rates in the following format

age........
30..........Male Non Smoker
30..........Male Smoker
30..........Female Non Smoker
30..........Female Smoker

Company B

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

Now there is no way we are going to enter the rates by hand because they would number a couple of thousand for just one company and that is only for Income Replacement Insurance. And also keep in mind that they go about doing the rates differently. Some will use a factor and some will use a rate for different issues that are rated.

If I could stick it all in the one table then that would be better if only for the reason that the DLookups etc will reference the same field names.

Even at the most basic level of variation some companies express waiting periods on Income Replacement as 30 days, 60 days and 90 days etc through to 2 years whereas others do 4 weeks, 8 weeks and 13 weeks etc which is not the same.

Would you really attempt to get each company into the same table Or let's say the same group of tables as there are other tables that that are One to Many against the base rates such as occupational rating etc. although that also adds a complication because the insurance companies for Income Replacement do not rate all the occupations the same, that is, one company might rate a surgeon the same as a solicitor or barrister but another company might give them a different rating. The companies also vary on the premium loading factor for different occupations classes.

But would you seriously try and get them into one table?

Mike

 

[Brianwarnock]

Hum...

Then I quess there is nothing left for me to say, Sorry I wasted your time...

ken

I posted this on another thread sometime ago, I stand by it and don't respond to Mike's theories, but I realise that more able people than I get frustrated if they believe that Mike is giving out misleading advice.

quote
I think Mike is just trying to wind us up now, I thought that he realised that his application could be done better by an experienced IT professional but quite rightly was taking the pragmatic point of view that
a It was no longer being developed
b was going to remain on the same software platform
c had worked for 7-8 years and needed to last only 2-3 more
d would cost too much to redevelop for no gain

but it now appears that he believes it is the way to go!!

end quote

Brian

 

[Kodo]

Kevin

IF anyone comes with suggestions. But people don't do that. They just keep repeating "you must normalise"

oddly enough.. that is the answer.. how many other ways can one say it.

Your main accounts should be related to another table that has your required fields in it (the ones that you say are never the same for each company).

Know what.. I'm gonna bow out right here. If you haven't gotten it by now then you won't get it from me trying to explain it.

Good luck Mike.

 

[Uncle Gizmo]

I've lost the will to live. . . . . . . . . . yawn

Col

It's like a road accident, you know it's not going to be nice but you still have to look.

 

[Uncle Gizmo]

Know what.. I'm gonna bow out right here. If you haven't gotten it by now then you won't get it from me trying to explain it.

Good luck Mike.

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