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

[GPGeorge]

Again, I truly appreciate everyone's input and efforts. This is what I've decided to do.

First, there's no doubt that those that recommended normalizing the database and using a separate table are telling me the "right" way to approach this. I use quotation marks, because even though I understand and fully agree that it's technically the correct approach, after careful consideration I don't think it's the best solution for me in this instance. I really need the data on one screen, and any subform situation will be awkward and impractical for my particular needs.

I searched around and found this excellent code from Allen Browne that does exactly what I need it to do, and at the same time it allows me the relatively simple entry process on the screen.

Function MinOfList(ParamArray varValues()) As Variant
    Dim i As Integer        'Loop controller.
    Dim varMin As Variant   'Smallest value found so far.

    varMin = Null           'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
            If varMin <= varValues(i) Then
                'do nothing
            Else
                varMin = varValues(i)
            End If
        End If
    Next

    MinOfList = varMin
End Function

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 all again for taking the time to work with me on this. I have the highest respect for all of you.

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

 

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

 

[MajP]

but replace one with another, you also have to amend the form

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.

 

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

 

[Pat Hartman]

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

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

 

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

 

[Pat Hartman]

If you've never looked at my bound, denormalized data form, here's a link.

Bound Denormalized Form

I've left the old versions here but ALWAYS go to the last post to get the current version. This database contains two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is...

www.access-programmers.co.uk

I created it when a client wanted a denormalized interface for a forecasting app. I never used it for anything else. It works fine for 12 months for a year so will also work for 7 days for a week. Not sure if it could handle 31 days for a month. I never tried. I wouldn't even consider it for a group that didn't have a fixed maximum but I did include an example that showed expenses. I think that is impractical because you need to modify the queries if you add new expense types. I suppose if you set a hard limit for types of expenses, and you were willing to generate the queries by writing code, you could make it work.

What I like is that the form looks like a spreadsheet but it is actually bound to a fixed number of queries that creates the "across" element and the form is bound so you only need a couple of lines of code for each column across.

 

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

 

[MajP]

If you've never looked at my bound, denormalized data form, here's a link

I will try to take a look at this before my presentation because many of the examples I use in the presentation are creative things I have seen others do.

What I like is that the form looks like a spreadsheet but it is actually bound to a fixed number of queries that creates the "across" element and the form is bound so you only need a couple of lines of code for each column across.

That is the gist of the presentation. Don't make your data into a spreadsheet, build a display that looks and acts like one but keeps the data normal.

 

[MajP]

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

 

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