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

[MajP]

Wow! So much work and effort to help me. Thank you seems a bit shallow

My point has been that it is not that much work.
The only hard part is dragging out the actual requirements.

This is just a demo so there is likely more to it, but if you agree to the concept we can walk you through it.

The reason we are all adamant is because we have see a ton of users come here and ask for help after it is too late. The have tons for forms, reports, and code but nothing is working and they cannot go any further. We try to help but without a ton of crazy work arounds there is nothing to do. Lots of tears when you tell them a couple month of work is completely unusable.
It is like building a house where the frame is not level, flat, square, and plum. No matter what you do things are not going to work well.

If you want to do this write show us your tables and relationship diagram. We can help you ensure that is proper. By looking at your posted form, I have suspicions it is not.
If someone's structure is correct I can help them get any features, user interface, forms, reports, and queries they want. If it is not I may not be able to help them do a trivial task.

 

[MadPiet]

Oh, MajP brings up a significant point. If your database is properly designed and you want to add a "feature" (something like a set of tables, some queries and some reports in order to answer a specific set of questions), then the expansion is easy. You just have to look at the database ERD and figure out where the tables should be attached. They expand gracefully. If you have to do rework and retrofitting, not a good design.
MajP is dead on. We know, because we've seen it over the last 5,10, 20 years. If you want a test, build the students and classes database from scratch. If that doesn't teach you the merits of full normalization, nothing will.
"Use the Force, Luke!"

Seriously, post your table designs. I'll walk you through fixing them. And when I think I'm right, I'll post the solution here so people can tear my design apart. Up to you.

 

[Isaac]

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.

You could have still had 5 fields on the Form, with each one looking up a different company name...

 

[Isaac]

My point has been that it is not that much work.
The only hard part is dragging out the actual requirements.

This is just a demo so there is likely more to it, but if you agree to the concept we can walk you through it.

The reason we are all adamant is because we have see a ton of users come here and ask for help after it is too late. The have tons for forms, reports, and code but nothing is working and they cannot go any further. We try to help but without a ton of crazy work arounds there is nothing to do. Lots of tears when you tell them a couple month of work is completely unusable.
It is like building a house where the frame is not level, flat, square, and plum. No matter what you do things are not going to work well.

If you want to do this write show us your tables and relationship diagram. We can help you ensure that is proper. By looking at your posted form, I have suspicions it is not.
If someone's structure is correct I can help them get any features, user interface, forms, reports, and queries they want. If it is not I may not be able to help them do a trivial task.

I agree with Maj, the suggestion to normalize isn't just to benefit this situation, it's to literally save your as** and your entire project.
Definitely normalize no matter how you fix this situation.
Normalization is the foundation of an entire database - without that you have created nothing more than a dangerous toy.
It's like making a car with no engine, then having a problem starting it, then saying I'm going to fix it by lighting a fire under the pistons.
That may solve you going somewhere one time, but you still have no viable car.

 

[bcmarshall]

Maybe post your proposed table definitions before you go too far. Better to have your design proofread before you spend a ton of time building on top of something that's not going to serve you very well long term. As they say "an ounce of prevention..."

Table definitions:
CVSource - Holds data on Dealers/Sources, one to many relationship on PK SourceID to CV referrals. It holds company name, contact name, contact info, etc. All text fields are nvarchar of various lengths.

CVReferrals has PK CVID, and holds individual customer information, such as name, address, DOB, etc. It also contains information related to the quote if it is sold and turned into a policy. That info would include Final Company (which company was finally selected), Final Premium (amount the customer actually paid for coverage), EffDate (policy effective date), and then, should policy later cancel, a cancel checkbox, cancellation date, cancel premium (how much was refunded), and a notes field.

CVInsQuotes, PK IQID, has many to one relationship with CVReferrals, linked on CVID. It has 4 columns, the PK, CVID, Company, and quote. Company is nvarchar(50), and quote is money.

CVOtherCompanies, PK ID, is only used as a row source with company names listed. for controls.

 

[bcmarshall]

Oh my God! Thank you thank you! You are just re-emphasizing all may points in preparation for my upcoming presentation.

And here the snowball starts. What was easy and trivial is now some wacky Rube Goldberg invention.
View attachment 120362

Again, it is just going to get worse and worse and sillier and sillier work arounds. Your very first post to highlight the min quote demonstrates that you have to come up with a wacky work around to do a trivial task. Now it is getting wackier.

@Pat Hartman , The only reason I am keeping at it is because I am assuming I might not be as clear and articulate as I think I am. Because what I propose seems so simple and clean and flexible to me I cannot get why anyone would bother with the non-normal less useable workaround.
Is it me?

I get it if the proposed solution was something complex, but this is nothing but a simple insert query on the new record.

I've already acknowledged the error of my ways and I'm working on changing it to a normalized configuration.

One question though. Can I open the subform with the first three datasheet company names defaulting to the three named fields I currently have?

 

[cheekybuddha]

All text fields are nvarchar of various lengths.

Is this in a server RDBMS (eg not in Access)?

If so, please provide CREATE TABLE statements for your tables.

 

[CJ_London]

Can I open the subform with the first three datasheet company names defaulting to the three named fields I currently have?

In a normalised structure you could have a default field or a sort order field in the company table

 

[Pat Hartman]

The only reason I am keeping at it is because I am assuming I might not be as clear and articulate as I think I am. Because what I propose seems so simple and clean and flexible to me I cannot get why anyone would bother with the non-normal less useable workaround.
Is it me?

You are probably the most clear and articulate expert who posts here But, I think you may have worn him down

 

[MadPiet]

Table definitions:
CVSource - Holds data on Dealers/Sources, one to many relationship on PK SourceID to CV referrals. It holds company name, contact name, contact info, etc. All text fields are nvarchar of various lengths.

CVReferrals has PK CVID, and holds individual customer information, such as name, address, DOB, etc. It also contains information related to the quote if it is sold and turned into a policy. That info would include Final Company (which company was finally selected), Final Premium (amount the customer actually paid for coverage), EffDate (policy effective date), and then, should policy later cancel, a cancel checkbox, cancellation date, cancel premium (how much was refunded), and a notes field.

CVInsQuotes, PK IQID, has many to one relationship with CVReferrals, linked on CVID. It has 4 columns, the PK, CVID, Company, and quote. Company is nvarchar(50), and quote is money.

CVOtherCompanies, PK ID, is only used as a row source with company names listed. for controls.

Lemme try this again...
Now that I have table definitions...

use tempdb;
go
/*
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.
*/
/* SETUP */
CREATE TABLE InsQuote(
    QuoteDate DATE DEFAULT GETDATE(),
    [Progressive] DECIMAL(6,2),
    [National General] DECIMAL(6,2),
    [Geico]  DECIMAL(6,2),
    [SafeCo]  DECIMAL(6,2),
    [Liberty Mutual] DECIMAL(6,2)
);
GO
INSERT INTO InsQuote VALUES ('1/1/2025',100.0,105.0,100.0,110.0,115.0),
('2/1/2025',105.0,100.0,110.0,112.0,110.0);

/* SOLUTION */
;WITH cteInsQuotes(QuoteDate, InsRate, Company)
AS (
    SELECT iq.QuoteDate,FixedQuotes.Rate, FixedQuotes.Company
    FROM InsQuote iq
    CROSS APPLY (VALUES(Progressive,'Progressive'),
                        ([National General],'National General'),
                        ([Geico],'Geico'),
                        ([SafeCo],'SafeCo'),
                        ([Liberty Mutual],'Liberty Mutual'))
                FixedQuotes(Rate, Company)
)
SELECT DISTINCT q.QuoteDate, topRates.Company, topRates.InsRate
FROM cteInsQuotes q
CROSS APPLY (
    SELECT TOP (1) iq.Company, iq.InsRate
    FROM cteInsQuotes iq
    WHERE q.QuoteDate = iq.QuoteDate
    ORDER BY iq.InsRate ASC
    ) topRates

 

[Pat Hartman]

@MadPiet Please step aside. You are only confusing the issue. The OP knows how to do it his way. He doesn't need another non-normalized alternative.
PS the values in the fields named after the insurance companies isn't their name, it is the dollar value of their quote so I have no idea where you are even going with this code.

 

[MadPiet]

Final Answer:

/*
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.
*/
USE tempdb;
go

CREATE TABLE Customer(
    CustomerID INT,
    FirstName VARCHAR(10) NOT NULL,
    LastName VARCHAR(10) NOT NULL,
    Geico SMALLMONEY,
    LibMut SMALLMONEY,
    NatGen SMALLMONEY,
    Progressive SMALLMONEY,
    SafeCo SMALLMONEY
);
GO
INSERT INTO Customer(CustomerID, FirstName,LastName,Geico,LibMut,NatGen,Progressive,SafeCo) VALUES (1,'John','Smith',null,null,300,200,null);
INSERT INTO Customer(CustomerID, FirstName,LastName,Geico,LibMut,NatGen,Progressive,SafeCo) VALUES (2,'Pam', 'Brown', null, null, 123, 500,null);
GO


WITH cteCustomer(CustomerID, FirstName, LastName)
AS (
    SELECT DISTINCT c.CustomerID, c.FirstName, c.LastName
    FROM Customer c
),
cteBids(CustomerID, Insurer, Bid)
AS (
    /* untwist the columns into rows */
    SELECT c.CustomerID, ca.Vendor, ca.Bid
    FROM Customer c
    CROSS APPLY (VALUES (CustomerId, 'Geico',[Geico]),
                        (CustomerID, 'Liberty Mutual',[LibMut]),
                        (CustomerID, 'Nat General', [NatGen]),
                        (CustomerId, 'Progressive', [Progressive]),
                        (CustomerID, 'SafeCo',[Safeco])
                ) ca(CustomerID, Vendor,Bid)
    )
/* return the top 1 (lowest) bid for each customer */
SELECT TOP 1 b.CustomerID, b.Insurer, b.Bid
FROM cteCustomer c
INNER JOIN cteBids b ON c.CustomerID = b.CustomerID
WHERE b.Bid IS NOT NULL;

 

[MadPiet]

In invisible ink, so nobody can read it.

Here's the data as described in his first post.
/*
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.
*/
USE tempdb;
go

/* this is really a customer and all his bids */
CREATE TABLE Customer(
CustomerID INT,
FirstName VARCHAR(10) NOT NULL,
LastName VARCHAR(10) NOT NULL,
Geico SMALLMONEY,
LibMut SMALLMONEY,
NatGen SMALLMONEY,
Progressive SMALLMONEY,
SafeCo SMALLMONEY
);
GO
INSERT INTO Customer(CustomerID, FirstName,LastName,Geico,LibMut,NatGen,Progressive,SafeCo) VALUES (1,'John','Smith',null,null,300,200,null);
INSERT INTO Customer(CustomerID, FirstName,LastName,Geico,LibMut,NatGen,Progressive,SafeCo) VALUES (2,'Pam', 'Brown', null, null, 123, 500,null);
GO

/* each WITH() is just a virtual table or query */
WITH cteCustomer(CustomerID, FirstName, LastName)
AS (
SELECT DISTINCT
CustomerID,
FirstName,
LastName
FROM Customer
),

cteBids(CustomerID, Bid, Insurer) AS
(
SELECT c.CustomerID, c.Geico AS Bid, 'Geico'
FROM Customer c
WHERE c.Geico IS NOT NULL
UNION ALL
SELECT c.CustomerID, c.LibMut, 'Liberty Mutual'
FROM Customer c
WHERE c.LibMut IS NOT NULL
UNION ALL
SELECT c.CustomerID, c.NatGen, 'National General'
FROM Customer c
WHERE c.NatGen IS NOT NULL
UNION ALL
SELECT c.CustomerID, c.Progressive, 'Progressive'
FROM Customer c
WHERE c.Progressive IS NOT NULL
UNION ALL
SELECT c.CustomerID, c.SafeCo, 'SafeCo'
FROM Customer c
WHERE c.SafeCo IS NOT NULL
)
SELECT c.CustomerID, c.FirstName, c.LastName, lowestBid.Bid, lowestBid.Insurer
FROM Customer c
CROSS APPLY (
SELECT TOP 1 b.bid, b.Insurer
FROM cteBids b
WHERE b.CustomerID = c.CustomerID
ORDER BY b.bid ASC
) lowestBid
ORDER BY c.FirstName, c.LastName;

 

[bcmarshall]

Is this in a server RDBMS (eg not in Access)?

If so, please provide CREATE TABLE statements for your tables.

/****** Object: Table [dbo].[CVInsQuotes] Script Date: 6/25/2025 3:43:48 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CVInsQuotes](
[IQID] [int] IDENTITY(1,1) NOT NULL,
[CVID] [int] NOT NULL,
[Company] [nvarchar](25) NULL,

[money] NULL,
CONSTRAINT [PK_CVInsQuotes] PRIMARY KEY CLUSTERED
(
[IQID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



/****** Object: Table [dbo].[CVOtherCompanies] Script Date: 6/25/2025 3:45:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CVOtherCompanies](
[ID] [int] IDENTITY(1,1) NOT NULL,
[InsCo] [nvarchar](25) NULL,
CONSTRAINT [PK_CVOtherCompanies] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



/****** Object: Table [dbo].[CVReferrals] Script Date: 6/25/2025 3:46:17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CVReferrals](
[CVID] [int] IDENTITY(1,1) NOT NULL,
[EntryDate] [datetime] NULL,
[PhoneAgent] [nvarchar](30) NULL,
[Source] [nvarchar](100) NULL,
[Contact] [nvarchar](50) NULL,
[Company] [nvarchar](50) NULL,
[First] [nvarchar](50) NULL,
[Last] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
[ZIP] [nvarchar](15) NULL,
[City] [nvarchar](60) NULL,
[State] [nvarchar](2) NULL,
[Phone] [nvarchar](15) NULL,
[nvarchar](75) NULL,
[Agency] [nvarchar](10) NULL,
[Types] [nvarchar](50) NULL,
[Prog] [money] NULL,
[NatGen] [money] NULL,
[Geico] [money] NULL,
[Company1] [nvarchar](25) NULL,
[Company2] [nvarchar](25) NULL,
[Company3] [nvarchar](25) NULL,
[Co1Amt] [money] NULL,
[Co2Amt] [money] NULL,
[Co3Amt] [money] NULL,
[FinalPrem] [money] NULL,
[FinalCo] [nvarchar](25) NULL,
[PolicyNumber] [nvarchar](25) NULL,
[EffDate] [datetime] NULL,
[Cancel] [bit] NOT NULL,
[CancelDate] [datetime] NULL,
[CancelPrem] [money] NULL,
[Notes] [nvarchar](max) NULL,
[SourceID] [int] NULL,
[PhoneDigits] [int] NULL,
[NameCombined] [nvarchar](50) NULL,
CONSTRAINT [PK_Referrals] PRIMARY KEY CLUSTERED
(
[CVID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[CVReferrals] ADD CONSTRAINT [DF_Referrals_Cancel] DEFAULT ((1)) FOR [Cancel]
GO



/****** Object: Table [dbo].[CVSource] Script Date: 6/25/2025 3:47:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CVSource](
[SourceID] [int] IDENTITY(1,1) NOT NULL,
[Source] [nvarchar](75) NULL,
[Rep] [nvarchar](50) NULL,
[First] [nvarchar](30) NULL,
[Last] [nvarchar](30) NULL,
[Contact] [nvarchar](50) NULL,
[Position] [nvarchar](30) NULL,
[Address] [nvarchar](75) NULL,
[Zip] [nvarchar](5) NULL,
[City] [nvarchar](25) NULL,
[State] [nvarchar](2) NULL,
[Phone] [nvarchar](15) NULL,
[Ext] [int] NULL,
[Fax] [nvarchar](15) NULL,
[CSZip] [nvarchar](50) NULL,
[RepState] [nvarchar](2) NULL,
[EMail] [nvarchar](75) NULL,
[SourceCombined] [nvarchar](75) NULL,
[BrokerFee] [money] NULL,
[SetupText] [nvarchar](18) NULL,
[1stQuoteDate] [datetime] NULL,
[1stQuoteID] [int] NULL,
[MostRecentQt] [datetime] NULL,
[MostRecentPol] [datetime] NULL,
[SetupDate] [datetime] NULL,
[RepCode] [int] NULL,
[SubCode] [int] NULL,
[PhoneDigits] [int] NULL,
[ForeCode] [int] NULL,
[Notes] [nvarchar](max) NULL,
CONSTRAINT [PK_CVSource] PRIMARY KEY CLUSTERED
(
[SourceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 

[bcmarshall]

In a normalised structure you could have a default field or a sort order field in the company table

How would I create three default fields when the subform is opened?

 

[CJ_London]

Use a filter on the company table in the query you use to populate the form

Edit - to create the field, you go into table design

 

[MajP]

1. Add customer
2.

3. If you select yes

4. Modify Choices

This would also allow you to have an Add button. Lets say you did a bunch of leads with companies 1,2,34,5. Then the boss says to do 6,7 also.

You can hit the add button and whatever was not included previously will be included.

Now this does not delete existing quotes from companies not in the list. You could either code that or filter them out of the subform if they are not defaults. However if you filter them out the code I used to highlight the min quote would also have to exclude the non-default values.

 

[Minty]

I had a battle like this for a client, who was desperate to have a spreadsheet style method of dealing with some of their data.

It involved variable number of"steps" in a process.
A process could any number of steps 1, or maybe 6 or 7 steps (max according to the user). Each process can be applied to any number of end customers, each with their own client manager..
They needed to record when these steps were carried out, and the process list would also constantly grow.
Each process could have unique steps.

I stored the data correctly, but to give them the layout they required did something similar to @MajP initial example.
I set it up to allow the editing form to have up to 15 steps and that it would not be cheap to redo it for more.

Interestingly 9 steps appears to be the most they have got to in a process. Glad I built it to handle more...

So this is simple.

The editing form is a whole different story:

This is editable, filterable etc. as it is a temporary table.
The labels are assigned at the point of the form opening, and the number of visible columns changes to accommodate the number of steps.
If data is changed we can work out what step, process and client it belongs to, and write it back to the normalised source table:

There is a locking mechanism to prevent multiple users modifying the same process at the same time, as being a local table it could get very messy. They can however load the same data for view only.

Despite my objections to the requirement to "Make me a spreadsheet", it actually works well, and they are really happy with it.