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

[Gasman]

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.

The Parable of the Beautiful Tree

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

 

[jdraw]

I like the parable George. We've all been there. The other "beautiful tree" scenario is "I've got so much time and effort into this that I can't possibly stop and correct the design flaw"!

 

[MajP]

I can maintain the simplicity of entry I want, and still get the answers that I need.

I am not disputing the correctness of what was offered. You guys are right on. It's simply a command decision on my part that I'm making for this particular application. Even though not technically correct, I think it will provide me with the most benefit overall.

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.

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

 

[Gasman]

And if you do not add more companies, but replace one with another, you also have to amend the form.

 

[Gasman]

In that case at least you would not have controls to add/delete. You just have to change the control source

If the number of companies was very stable then this might be pretty feasible and you modify it when the time comes.

If it could change a lot then you could do a more dynamic form with some code. I could have done it here but did not want to scare off the OP more than what is already there. But to give it more flexibility

1. Pick a reasonable amount of columns and create text boxes for those. lets say you think you will never track more than 15 companies
2. Create the same amount of labels
3. When the form opens it reads the crosstab and determines the amount of companies
4. Show/hide the controls and position and size the columns. Maybe only use 5 now but you have 10 more for the future.
5. bind the controls to the available columns and set the labels

In the presentation I am doing, I have an example where I do something similar.

Yes, I took a quick peek as to how you did it.

Nice job. I look forward to your presentation.

 

[bcmarshall]

I posted that in #10

I know you did. Sorry I didn't give you credit for it. It was very helpful.

 

[MajP]

A little update.
Now the form list the company and the lowest bid. (does not handle ties)

Also fixed it when you open the pop up it returns to the same cell.

 

[GPGeorge]

In that case at least you would not have controls to add/delete. You just have to change the control source

If the number of companies was very stable then this might be pretty feasible and you modify it when the time comes.

If it could change a lot then you could do a more dynamic form with some code. I could have done it here but did not want to scare off the OP more than what is already there. But to give it more flexibility

1. Pick a reasonable amount of columns and create text boxes for those. lets say you think you will never track more than 15 companies
2. Create the same amount of labels
3. When the form opens it reads the crosstab and determines the amount of companies
4. Show/hide the controls and position and size the columns. Maybe only use 5 now but you have 10 more for the future.
5. bind the controls to the available columns and set the labels

In the presentation I am doing, I have an example where I do something similar.

Once, years ago, when still working for a living (instead of kibitzing in on-line forums) we had a client that insisted on a denormalized data entry form for appointments on days of the week. That was doable, probably using an approach similar to yours.

However, since there are only 7 days in a week, and only 5 workdays, we were able to count on it being stable over time. Big difference from this scenario.

It was relatively fast, as I recall, despite having to transform data in and out.

I'm looking forward to your presentation to see how you did it.

 

[MajP]

a denormalized data entry form for appointments on days of the week. That was doable, probably using an approach similar to yours.

That is actually one of the examples I have in the presentation, and I also one I cite as a common problem areas that I see. I call it "The Scheduling" problem. Users will often create very non-normal data structures just to support that display. The presentation focuses on creative ideas that I have seen other people do or ones I have come up with to create alternate "non-normal" displays while ensuring the data remains normal.

 

[MajP]

Doing that simply kills all ability to do analysis of past data.

I think what @Gasman is saying is that I hardcoded 5 columns of the Crosstab. In theory the crosstab could have more Insurance companies, but I choose these 5 to bind. I could "replace one with another" by binding one of those columns to a column that is not already bound. I do not think he was suggesting changing the name or deleting from the data table.

But again that is a limitation of doing this that a traditional form / sub form will not have.
If I was going to try to do this for real I would need to make it more robust like the steps in thread #26. The control source of the columns needs to be dynamic. I would read the actual crosstab and
1. figure out how many columns
2. Show, hide, position that many controls
3. Bind the columns and set the labels.

 

[bcmarshall]

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.

 

[bcmarshall]

Doing that simply kills all ability to do analysis of past data.

Actually, data analysis is a very big piece of our company's legacy. The way I've got it structured we will be able to analyze sales, company quotes, trends, and everything else needed.

I've been developing this DB for 15 years, and because of the reports available, the owner knows more about what's happening within his business than probably 95% of small business owners. This new information will be no less accessible to him.

I'm not trying to convince you guys that I'm right. If someone were to ask me for advice, I'd tell them exactly the same thing that you're telling me. There's no doubt that the right way to proceed is through normalized data.

I'm simply working with the overall picture, my knowledge of company dynamics, my intimate understanding of the boss's desires, and my ultimate concern that this whole thing works as advertised.

 

[bcmarshall]

@Pat Hartman. I will probably add a link in my presentation to your presentation. I do not do a deep dive into a specific solution like you do, but show a myriad of ideas of "what is possible". It would be good for participants to see a more flushed out example too.

Thank you for understanding. I am doing exactly that, working with "what is possible". My goal, and the company goal, is to have something that works the best for them. I think I've chosen correctly. Time will tell, and I'm the kind of guy that will return to this thread in three years and tell you how badly I screwed up, or how successful it's been.

 

[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

I'd like to include a screenshot of my main data entry form. I hope it helps others to understand why I made the choices I did.

By the way, this is a database within a database. The company has been up and running for 15+ years, and this part of the development is to accommodate a new acquisition which specializes in a totally different vehicle type. None of what's happening in this portion of the system is commingled with the existing system, so that's why I'm starting from scratch.

Everything is new...forms, tables, and reports.

 

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