I need the minimum value among 5 different fields in one record (5 Viewers)

[bcmarshall]

Thank you, thank you! This is the perfect segue, and I will make a slide of that quote above for my presentation as a lead in. If you do not mind.
I am doing the next Access Europe presentation in July 2 on this exact topic.

This is the dilemma. You want to keep the data normal, but the native Forms and Subforms do not make the user interface as intuitive as you want or as easy to use. So instead users do exactly what you are doing and make poor decisions to use non-normal data just to support a user interface.

What if you can have best of both worlds? Normalized data and a more intuitive interface.

So here is my form.
View attachment 120334
The data is stored like all the experts told you to do it.
Customer table
Company table
Junction table

1. There is some code involved in doing this, but not much
2. You have to build some queries but not too challenging
3. You can not type in the cells but if you try it opens the pop up to the correct location
4. Unlike a form subform, if you add more insurance companies you will have to redesign the form


FYI @isladogs

You have my permission to use the quote, not that you needed it. But thanks for asking.

I did not come to this forum, hat in hand, seeking help from a bunch of caring strangers with common interests, with the intention of just ignoring what was so sincerely offered. You guys are superstars, and I really mean that.

I charted my own path after giving serious consideration to your collective input. I hope I made the right call.

 

[bcmarshall]

You can lead a horse to water, but you cannot make them drink.

I drank. I also tasted the water from other wells.

Please don't confuse a conscious, thought-out decision (even if it's one you would not recommend), with obstinacy. They are in no way the same thing.

 

[MajP]

@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]

Spoiler:

@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.

I spent 20 years in the insurance business myself, both outside as an agent and inside as an employee.

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.

 

[cheekybuddha]

I hope I made the right call.

Spoiler: ...

You didn't!

(Not wanting to be mean; we've all been where you are, and have thought we could 'get away with it'. Grim experience is the reason why we are all trying to advise against this path.)

 

[cheekybuddha]

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.

Remember, you are the developer - it's your job to convert the logical structure into the interface your client was to see and use.

How it works behind the scenes is of no concern to the client.

 

[Pat Hartman]

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.

So, if you have to add a new company, you are planning on changing the name of an existing one? That eliminates any ability in the future to analyze old data.

I think I've chosen correctly.

Then there is nothing more the experts can do to help you. Good luck.

PS, my example of the bound, denormalized form shows what you can do with the interface when your data is properly normalized. If you understood the example, you would see that you could properly normalize the data and yet the customer gets to use a form formatted the way he likes. The client isn't the expert. He doesn't get to dictate the internal architecture of the application. The client gets to influence the presentation of the data, not how it is stored.

I agree with Maj, stick with spreadsheets.

 

[MajP]

The solution to keep the data normalized, provide the functionality you want, and more is so simple, it is just dumb not to do it.

Lets assume at any give time you are going to want 5 to 8 default quotes. This may change periodically
1. In the Company table add a field so that you can identify which ones are required by default
2. You would have a form to open this table and change the defaults when needed.

In this case I tagged the first 5, but the beauty in this design it can change whenever you want it. And if you want more or less that can change.

3. Now when I enter a new lead I am going to require the defaults when I create a new lead. If I change the defaults or add to the list I get a new set of defaults.

4. When I create a new lead I do something like this, and have to add a user name since a lead without a name does not make sense.

5. I add a name and Voila, the default quotes are ready to be filled in

For some reason you adverse to subforms although that is pretty standard in any database. However with a little formatting abouve like removing record selectors, removing border, removing colored header you would not know the five records are on a subform.

6. Each time I add a quote conditional formatting will show the lowest quote

I did not do it because you are hung up on just 5, but at this point my design has the flexibility to now add more quotes, delete quotes, replace quotes.

This is as easy as what you have
Way more flexible
Supports all future capabilities and data analysis.

Am I missing any requirements or functionality?

 

[MajP]

BTW the boss would like to know the Min, Max, Average quote for the last year by company and how many we have.

Company_Name	Avg Quote	Max Quote	Min Quote	Number of Quotes
Geico	$641.25	$999.00	$123.00	4
Liberty Mutual	$595.00	$888.00	$323.00	5
National General	$511.20	$789.00	$123.00	5
Progressive	$505.00	$666.00	$200.00	4
Safeco	$399.33	$777.00	$100.00	3

That took me ten seconds because my data is normal.
Don't even try to come back and ask how to do this with your data. Requires a normalizing query or and/or lots of vba. If the defaults would change over time or you allow additional quotes then this above question is trivial in my design and a near impossible in your design.
And that is why we could care less that you got one form to kind of work, for now, with a hope that nothing changes and no new requirements.

 

[MadPiet]

You have my permission to use the quote, not that you needed it. But thanks for asking.

I did not come to this forum, hat in hand, seeking help from a bunch of caring strangers with common interests, with the intention of just ignoring what was so sincerely offered. You guys are superstars, and I really mean that.

I charted my own path after giving serious consideration to your collective input. I hope I made the right call.

Maybe post your design. Just an empty database. The thing I'm afraid of, having worked with messes like this, is that answering a simple question with a database that isn't designed correctly can be really hard. When I used to do this with databases I inherited, I'd map out the limitations of the current database design. The one thing I'd be afraid of is painting myself into a corner with a bad design.

 

[bcmarshall]

I spent 20 years in the insurance business myself, both outside as an agent and inside as an employee.

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.

I know you're absolutely sincere in your warning, and I do appreciate that, but please consider the following.

Worst case nightmare scenario is that after we have 10k or 50k records in the system, one of the three major companies named in the fields goes out of business, or, more likely, there's a falling out between our agency and the corporation. For whatever reason, we decide to end the relationship. Now what do I do?

If I got this news tomorrow I'd have it up and running in 20 minutes best case, 1 hour on the outside. I'd simply create a new column in the table, name it for the replacement company, and replace the control source and labeling for the control on the form to direct it to the new named field. The data related to that company would remain available, and all of their associated sales data would remain intact.

Please remember that we consider this to be an extremely unlikely scenario, and surely would not happen more than once given the size and stature of the named company fields. Only giant companies have named fields.

If it happened to be one of the 8 smaller companies I've listed in the table, no change would be needed at all. I'd just add the replacement company to the table, remove the one we're no longer working with, and go on with life.

If the owner decided to elevate one of the companies now in the table to be one of the three, I'd create a new named field for the elevated company, and run an update query to transfer all the business linked to it while it was listed as Company1, Company2, or Company3 to the new named field. Then I'd delete the data related to that company that's held in the generic fields so that everything would remain unchanged.

I know that what I've suggested is far more complicated than it would be if I went to a strictly normalized system, but I also think in this one limited instance my solution is the most reasonable when taking everything the company is considering into account.

 

[MajP]

f I got this news tomorrow I'd have it up and running in 20 minutes best case, 1 hour on the outside. I'd simply create a new column in the table, name it for the replacement company, and replace the control source and labeling for the control on the form to direct it to the new named field. The data related to that company would remain available, and all of their associated sales data would remain intact.

Please remember that we consider this to be an extremely unlikely scenario, and surely would not happen more than once given the size and stature of the named company fields. Only giant companies have named fields.

If it happened to be one of the 8 smaller companies I've listed in the table, no change would be needed at all. I'd just add the replacement company to the table, remove the one we're no longer working with, and go on with life.

If the owner decided to elevate one of the companies now in the table to be one of the three, I'd create a new named field for the elevated company, and run an update query to transfer all the business linked to it while it was listed as Company1, Company2, or Company3 to the new named field. Then I'd delete the data related to that company that's held in the generic fields so that everything would remain unchanged.

But I showed you that you can have your cake and eat it too. So why not? I get you did not want a traditional subform and force the user to add each company, but that has been handled.
All those problems above go away. Data entry is no different. You can make your form look like and do exactly what you want and more. You support future requirements. You can change the default companies with a checkbox. Support future reporting, summation, and analysis.
There is a little bit of up front code, but everything falls in place after that.

 

[bcmarshall]

BTW the boss would like to know the Min, Max, Average quote for the last year by company and how many we have.

Company_Name	Avg Quote	Max Quote	Min Quote	Number of Quotes
Geico	$641.25	$999.00	$123.00	4
Liberty Mutual	$595.00	$888.00	$323.00	5
National General	$511.20	$789.00	$123.00	5
Progressive	$505.00	$666.00	$200.00	4
Safeco	$399.33	$777.00	$100.00	3

That took me ten seconds because my data is normal.
Don't even try to come back and ask how to do this with your data. Requires a normalizing query or and/or lots of vba. If the defaults would change over time or you allow additional quotes then this above question is trivial in my design and a near impossible in your design.
And that is why we could care less that you got one form to kind of work, for now, with a hope that nothing changes and no new requirements.

Although it may appear that way, I assure you that your collective comments have not fallen on deaf ears. I've been listening carefully.

These conversations have made me realize that right now my development is technically a hybrid. I have quasi-normalized data in the three generic fields whose data is drawn from a table (Company1, 2, 3), and three fields that are not normalized. I say quasi-normalized because it's currently three company fields rather than one, but it is still in a normalized format.

I'd like to ask a different question.

Suppose I replaced the three named fields with three drawn from the table as the others are, (Company4, 5, 6). I can default value the controls to the names I want, and yet I'll have the ability to change everything in an instant.

In this scenario we don't have one company column and one quote amount column. We have six but they can all be combined via query, so I can still have what amounts to a normalized database.

I kinda like this solution in that it takes into account what everyone has been saying but still offers the entry simplicity that we're seeking.

Wondering what your reaction is to that? Is it better, worse, or lipstick on a pig?

 

[GPGeorge]

Although it may appear that way, I assure you that your collective comments have not fallen on deaf ears. I've been listening carefully.

These conversations have made me realize that right now my development is technically a hybrid. I have quasi-normalized data in the three generic fields whose data is drawn from a table (Company1, 2, 3), and three fields that are not normalized. I say quasi-normalized because it's currently three company fields rather than one, but it is still in a normalized format.

I'd like to ask a different question.

Suppose I replaced the three named fields with three drawn from the table as the others are, (Company4, 5, 6). I can default value the controls to the names I want, and yet I'll have the ability to change everything in an instant.

In this scenario we don't have one company column and one quote amount column. We have six but they can all be combined via query, so I can still have what amounts to a normalized database.

I kinda like this solution in that it takes into account what everyone has been saying but still offers the entry simplicity that we're seeking.

Wondering what your reaction is to that? Is it better, worse, or lipstick on a pig?

TRY what MajP has been offering. It's the perfect compromise.

Data is either normalized or it is not normalized. Please disabuse yourself of that "quasi-normalized" misunderstanding.

 

[MadPiet]

Since I don't know your exact structure, but I've dealt with this before, I'll use my example. A long time ago, I inherited a database where my job was basically to do frequency counts of symptoms and grades. (like the drug ads on TV that say "common side effects include mild [symptom]...")
What I inherited was structured like this:
CREATE TABLE SymptomReport(PatientID INT, Cycle TINYINT, Alopecia, Causality1, Relatedness1, Anemia, Causality2, Relatedness2...)

Might save a tiny bit of time entering data, but getting minimums and maximums across a single record would be a nightmare. I had to create a hideous view that unioned all the stuff together like this:

SELECT PatientID, Cycle, 'Alopecia' AS Symptom, [Alopecia] AS Grade, Causality1 AS Causality, Relatedness1 AS Relatedness
FROM SymptomReport
UNION ALL
SELECT PatientID, Cycle, 'Anemia' AS Symptom, [Anemia], Causality2, Relatedness2
FROM SymptomReport
UNION ALL
...

then I could summarize that union query... slow, but it works.

 

[Pat Hartman]

@MajP
The OP doesn't care about best practice and isn't interested in anything other than his own design which he thinks is fine. You've been more than generous with creating specific examples of how to implement the requirements with normalized tables. The OP simply isn't interested.

Suppose I replaced the three named fields with three drawn from the table as the others are, (Company4, 5, 6). I can default value the controls to the names I want, and yet I'll have the ability to change everything in an instant.

And your history is now meaningless.

 

[GPGeorge]

Although it may appear that way, I assure you that your collective comments have not fallen on deaf ears. I've been listening carefully.

These conversations have made me realize that right now my development is technically a hybrid. I have quasi-normalized data in the three generic fields whose data is drawn from a table (Company1, 2, 3), and three fields that are not normalized. I say quasi-normalized because it's currently three company fields rather than one, but it is still in a normalized format.

I'd like to ask a different question.

Suppose I replaced the three named fields with three drawn from the table as the others are, (Company4, 5, 6). I can default value the controls to the names I want, and yet I'll have the ability to change everything in an instant.

In this scenario we don't have one company column and one quote amount column. We have six but they can all be combined via query, so I can still have what amounts to a normalized database.

I kinda like this solution in that it takes into account what everyone has been saying but still offers the entry simplicity that we're seeking.

Wondering what your reaction is to that? Is it better, worse, or lipstick on a pig?

Does it matter to your client whether they can create a report summarizing the business they've done with every company for each one of the prior 10 years, by year or aggregated?

If that report would matter, you have no choice but to abandon the non-normalized approach.