GPGeorge
George Hepworth
- Local time
- Today, 04:36
- Joined
- Nov 25, 2004
- Messages
- 2,992
I spent 20 years in the insurance business myself, both outside as an agent and inside as an employee.@bcmarshall If I was you I would stop trying to build a database and go back to building spreadsheets only. I say that because your logic is coming from someone who builds a single worksheet and does not have the capacity to think at the whole application level.
1. You claim success because you can make this one form work and work well. Who gives a crap? I can make the worst designed database work well for a single form. I can make it get up and dance across the room with enough VBA. But database developers do not think like that. We design it so it works for all current and future forms and reports and flexible to handle all data analysis. If it is done correct everything is easy even future enhancements, requirements, that I do not know yet. I prepare it to do any possible reporting and analysis.
2. If you denormalize to work with this one form what is normally easy becomes hard and requires work arounds. This snowballs in all future development. Your case is a prime example. Sure I can now write code to do a trivial task (min across fields) that could be done in a query to find the minimum of quotes on a form. Normally you could do this automatically without a complex workaround for a trivial task. But that is only the beginning and it gets worse from here. But lets say in the future the boss asks can you give me the Max, Min, and average quotes by each company for the 1st quarter of 2025. Now you come back here asking us to do this. And again I can do it with a bunch of code to answer a trivial question that would take ten seconds with normalized data. Then the boss comes back and wants a form with all clients for the last year and their minimum quote. Now you are back here for us to write code for something that should take ten seconds in a query. If you are not thinking like this then you are better off building a spreadsheet.
3. I see your simple form, and hear your requirement. AGAIN WE CAN GIVE YOU THAT CAPABILITY WITHOUT DENORMALIZING THE DATA. I get it you do not want the user to have to pick the five companies. What if you add a user and automatically the 5 default companies appear on your form? But you could add more if you want? Not sure how that would not meet your needs.
Let me do another demo to see if we can meet your needs. I seem that as follows
1. Customer input form in single view
2. Five Chosen Companies (established ahead of time) that appear on the form
3. A way to enter the quotes
4. Highlight the min quote.
The demo I did was actually way harder then what I think you want based on your simple customer form.
But again let us try to help you meet the user experience requirements while still keeping the data normal.
GPGeorge wrote:
Unfortunately, there's no way to show you ahead of time what the consequences are going to be. As I posted earlier, it's a matter of the number of future billable hours of work that will be required when Safeco goes out of business and you have to add a new field for the replacement company.
Please understand that I'm not trying to argue that I'm right. I know the collective advice given here is the proper way to proceed, but there are several things that I didn't include in my introduction in order to keep it brief. I hope that by including them now, it will help to clarify my decision-making.
First, the owner of the company has about 40 years experience in the insurance business, and in his judgment we won't ever need more than five companies quoted for a given customer. We settled on six fields, actually, just to be on the safe side.
He selected the three major companies that he expects will handle the lion's share of the business, and then he gave me a list of eight additional companies that might be used.
Remember, I am accommodating the expertise of the owner and programming around his needs. I'm also targeting his desire to streamline the process of quoting, and eventually selling these policies.
To that end, I created a table containing the extra companies, and then, as well as the three fields named for companies, I called the last three Company1, Company2, and Company3. The on-screen label for those fields has been replaced with a combo box linked to the table of companies, so for those fields the processor can select the name of any of those additional companies listed in the table, and the dollar amount will still be linked to that name because the name will be stored as data in the field.
This configuration allows me to add companies at will without changing the basic structure, and still lets me work within the owner's sense that we won't ever need more than five total companies quoted for any given individual.
I have set it up so that as data is entered the backcolor of the field with the lowest quote will change to highlight it, so that without any human intervention of reviewing the quote amount and thinking, even for a few seconds, about which is the best, it's immediately identifiable. It's about saving time and minimizing errors, which are also part of my instructions from the owner.
I'm not trying to justify my choice and somehow convince you I'm right. I know it's not the best programming decision, but I hope the additional information helps you to recognize that it does serve the purposes of both my needs and the owner's desires.
And, in the final analysis, it satisfies the one overriding goal that's more important than anything else. Even if not done according to conventional wisdom, it does work, and actually works quite well.
I had a very good friend who told me that there was "madness in my method". He was obviously right.
Your client may need 5 companies at any given time.
He will end up replacing one of them within the very near future, and probably more than one. That's when this decision will come back to haunt you, not him. Because you are building in future billable work for yourself that he will have to pay for.
You even laid the groundwork for that to happen: "He selected the three major companies that he expects will handle the lion's share of the business, and then he gave me a list of eight additional companies that might be used."
I apologize that we can't convince you. I apologize in advance for the hard times to come.