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

[bcmarshall]

I have a database for an insurance company, and I'm trying to find the minimum value among 5 or more fields.

I have fields named for each of several companies. Imagine Progressive, National General, Geico, Safeco, Liberty Mutual. Each field is currency, and will contain the quote received from each of them. What I'm trying to accomplish is to compare those five fields and find the lowest quote among all of them.

The comparison is taking place among the fields on one record. I'm not looking for a minimum in the column of figures. I'm trying to make my verbal description clear, but all those fields are filled in for one customer, and I'm looking to find the lowest quote on that record.

As always, thank you in advance. I greatly appreciate the help the wizards here are so free with.

 

[theDBguy]

You'll need a custom function for that, but it might be easier to handle if you have a normalized table structure.

 

[June7]

Fairly common topic. Review
https://www.access-programmers.co.uk/forums/threads/max-of-multiple-columns.184618/
https://www.access-programmers.co.u...ue-in-multiple-fields-issue-with-null.235721/

 

[plog]

Agree with DBguy, you need to fix your tables not band-aid this.

I have fields named for each of several companies. Imagine Progressive, National General, Geico, Safeco, Liberty Mutual

You are storing data in field names, that is an incorrect way to store your data in a database. A general rule of thumb is that field names should be generic (CompanyName) and not specific (Geico). So instead of a field for every company that stores a value, you need a new table to hold all these values. It would be structured like so:

tblInsuranceCompanyValues
icv_ID, autonumber, primary key
ID_MainTable, number, this holds the autonumber value of the table all this data is currently in
icv_Company, text, this will store the name of the company (e.g. Geico, Safeco, Liberty Mutual, etc)
icv_Value, number, this will store the value

That's it, that one table with its 4 fields can now hold an infinite number of companies values for you. In 3 months when you get a new company you don't have to add a new field like you would now, you simply add a record to this table for that company. That's a huge benefit of not storing company names as field names.

Another huge benefit is that when you do this, finding the minimum, maximum, sum, count and even average is super easy with an aggregate function:

Sum data by using a query - Microsoft Support

Learn how to use an aggregate function in Access to sum the data in a query result set.

support.microsoft.com

 

[CJ_London]

You could always use a union query to normalise the data - but every time you add a new company, you’ll need to modify the query, as you would using a function

Better to normalise from the get go as already suggested

 

[GaP42]

Agree with DBguy, you need to fix your tables not band-aid this.



You are storing data in field names, that is an incorrect way to store your data in a database. A general rule of thumb is that field names should be generic (CompanyName) and not specific (Geico). So instead of a field for every company that stores a value, you need a new table to hold all these values. It would be structured like so:

tblInsuranceCompanyValues
icv_ID, autonumber, primary key
ID_MainTable, number, this holds the autonumber value of the table all this data is currently in
icv_Company, text, this will store the name of the company (e.g. Geico, Safeco, Liberty Mutual, etc)
icv_Value, number, this will store the value

That's it, that one table with its 4 fields can now hold an infinite number of companies values for you. In 3 months when you get a new company you don't have to add a new field like you would now, you simply add a record to this table for that company. That's a huge benefit of not storing company names as field names.

Another huge benefit is that when you do this, finding the minimum, maximum, sum, count and even average is super easy with an aggregate function:

Sum data by using a query - Microsoft Support

Learn how to use an aggregate function in Access to sum the data in a query result set.

support.microsoft.com

also requires the customerquoteid - as the icv_value is presumably the quoted policy premium value from the icv. There may be other common attributes of the quotes which might be used judge the best deal, but that was not indicated.

 

[bcmarshall]

Thank you all for your help. I'll restructure the tables.

I really appreciate all the input.

 

[Pat Hartman]

I have fields named for each of several companies. Imagine Progressive, National General, Geico, Safeco, Liberty Mutual. Each field is currency, and will contain the quote received from each of them.

How would you store the names of the dependents of your employees? Would you name them Tom, Dick, and Harry because your first employee had three children and those were there names? Would you name the Child1, Child2, and Child3? Both are very wrong.

 

[GPGeorge]

Here is a link to a blog article that explains the problem in some detail and describes the approach needed to resolve it. Note that article has internal links to other articles on the same topic.

 

[Gasman]

Allen Browne wrote something for such a situation.

Microsoft Access tips: MaxOfList() and MinOfList() functions

Code for selecting the minimum and maximum values of a list of values in a Microsoft Access database. Demonstrates ParamArray.

allenbrowne.com

Still best to normalise though.

 

[GPGeorge]

Early on my Access career, a situation exactly like this forced me to take normalization seriously, learn what it really involves, and how to avoid uncomfortable conversations with clients who want to know why it required so many billable hours to add one more field to a table to accommodate an additional category.

Whoever had created the original database had created what I came to call "a spreadsheet style table", just like the one in this thread with the names of several insurance companies. But they had to create a form with multiple controls to edit each of those multiple, repeating fields. Then, for reporting, they had to create a multiple clause union query to turn it into a usable format.

Fixing all of that to add another category would have required several hours of billable work for me.

If the original design had been properly normalized, no billable work would have been required because all they would have needed to do was add one more record to the table.

That was when I also figured out that billable hours is a two-edged sword. Sure, patching up the interface to support a non-normalized table starts the clock running. But the down-side to that is the client quickly gets tired of paying to correct past mistakes and starts to look around for alternatives to replace your database.

 

[bcmarshall]

I'm struggling with this.

I have created the second table exactly as plog suggested, Where I'm having the issue is in integration of that table into the source query for the form, and with the form fields themselves.

In my current form/table configuration, it's quite easy to create a control on the form named for each company, and recording that company's quote in the associated field. There are five fields, so layout is no problem.

In the new iteration, how do I lay out the form fields? I need all of the information displayed on one record, including all of the typical customer contact and other data. How do I actually separate the data at form level if there is only one Company field and one Quote value field?

 

[Gasman]

Use a subform?
So the quote is the mainform and all the proposers are shown in the subform.
You can sort on quote amount ascending, so the cheapest option is at the top.

 

[GaP42]

I'm struggling with this.

I have created the second table exactly as plog suggested, Where I'm having the issue is in integration of that table into the source query for the form, and with the form fields themselves.

In my current form/table configuration, it's quite easy to create a control on the form named for each company, and recording that company's quote in the associated field. There are five fields, so layout is no problem.

In the new iteration, how do I lay out the form fields? I need all of the information displayed on one record, including all of the typical customer contact and other data. How do I actually separate the data at form level if there is only one Company field and one Quote value field?

Just two tables?

Table one: Customer .. CustomerID autonumber and customer detail attributes ("Main table" in plog's post)
Table two: CustomerQuote .. as per plog suggested structure however do you need a QuoteDate? and QuoteID (InsuranceCompanyQuotes - icv - in plogs post), quote accepted flag is another attribute?
Perhaps an InsuranceCompany table is also needed - with a Foreign key to the insurer offering the quote in the CustomerQuote table

Have you set a relationship Customer may have 1:M to CustomerQuotes ?

A form for customer could display the quotes for that customer in a subform - sorted on value
or
A form listing all quotes (in what order? Request date?, excluding lapsed?) - showing customer name, insuranceCompanyName, quotereceivedvalue with the CustomerQuoteID.
Query - based on CustomerQuote table (all details), with join to Customer table to obtain Customer Name and join to InsuranceCompany table to obtain InsuranceCompanyName. Filter the list based on a search or combo by customer name and CustomerQuoteID, list results in value order

CustomerQuoteID was suggested as a customer may request any number of quotes at different times - post #6.

This is very much a simplified model. You need to fully work through all the requirements to manage the quotes

 

[plog]

Can you upload a copy of your database? If not, complete the Relationship tool, take a screenshot and then post it here.

Before you move on to forms its best to make sure your tables are correct. I just don't want to miss any other issues that will mean re-redesigning the forms.

 

[bcmarshall]

Plog, the table I created is exactly as you suggested, with only 4 fields. ID, CVID (ID field on table containing the customer data), Company, Quote.

I can link the tables in a query by CVID, but I now have only one field for company name and one field for quote amount that I can use on the form. That's the issue I'm trying to get around.

I could use a subform, but that's complicating things even more.

Still seeking guidance/suggestions.

 

[plog]

What you have now is a 1 to many relationship. Your existing table is the 1 and that new table I prescribed is the many. With forms you use a main form to display just 1 record from the 1 table and a subform to show as many records as needed from the many table.

Here's good documentation for how to build those forms in Access and below it is a screen shot from that page:

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

The above form shows 1 employee in the main form and many orders in the subform. That is how your form should be set up. The bottom portion would show all related records of the new table you created. Give the link above a read and it will walk you through how to do that

 

[mike60smart]

Plog, the table I created is exactly as you suggested, with only 4 fields. ID, CVID (ID field on table containing the customer data), Company, Quote.

I can link the tables in a query by CVID, but I now have only one field for company name and one field for quote amount that I can use on the form. That's the issue I'm trying to get around.

I could use a subform, but that's complicating things even more.

Still seeking guidance/suggestions.

As suggested upload a copy of the Database.

 

[bcmarshall]

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.

 

[Gasman]

I posted that in #10