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

[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

 

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

 

[bcmarshall]

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.

I really appreciate your comments, and those of everyone else. I'm working on the transition.

 

[The_Doc_Man]

First, the owner of the company has about 40 years experience in the insurance business,

I have avoided this thread before, because I happened on it late and you had a LOT of experts offering advice. However, when I caught up on this thread, I saw the above statement and HAD to step in. I saw in a later part of the thread that you are now planning to bring the DB into a normalized design, so I congratulate you. But I have to also admonish you (whip with a wet noodle 'cause it is a minor admonishment) regarding the error that led you into the contentious part of the exchanges.

YOU DON'T CARE how many years the company owner has put into the insurance business. The correct question is, how many years has he put in on successful database design? He will be your subject-matter expert. He can fill in the blanks on the visible steps required for this DB to do its thing properly. But YOU are the person who will have to implement this behind-the-scenes in the most efficient way possible given the data you have and the goal you want to reach.

Here's a hypothetical question: If you are feeling bad, do you go to an actuarial guy who is very old and thus has lots of life experience? Or do you ask the advice of a guy/gal who actually went to med school? The secret of success in building a database is asking the right questions of the right people at the right time so that you can figure out what you need to do to achieve your end or intermediate goals. NEVER confuse the business process with the computer instructions. You need to remember that there are things you do for display and things you do for computation. YOU get to do the computation. Your might let your boss have inputs on the display.

NEVER feel bad if you come to a point where you have to "sneak in" something to make it work the way the boss wants. As long as you keep things normalized, most DB actions will be so fast that nobody will recognize that anything complex happened at all.

 

[bcmarshall]

I have avoided this thread before, because I happened on it late and you had a LOT of experts offering advice. However, when I caught up on this thread, I saw the above statement and HAD to step in. I saw in a later part of the thread that you are now planning to bring the DB into a normalized design, so I congratulate you. But I have to also admonish you (whip with a wet noodle 'cause it is a minor admonishment) regarding the error that led you into the contentious part of the exchanges.

YOU DON'T CARE how many years the company owner has put into the insurance business. The correct question is, how many years has he put in on successful database design? He will be your subject-matter expert. He can fill in the blanks on the visible steps required for this DB to do its thing properly. But YOU are the person who will have to implement this behind-the-scenes in the most efficient way possible given the data you have and the goal you want to reach.

Here's a hypothetical question: If you are feeling bad, do you go to an actuarial guy who is very old and thus has lots of life experience? Or do you ask the advice of a guy/gal who actually went to med school? The secret of success in building a database is asking the right questions of the right people at the right time so that you can figure out what you need to do to achieve your end or intermediate goals. NEVER confuse the business process with the computer instructions. You need to remember that there are things you do for display and things you do for computation. YOU get to do the computation. Your might let your boss have inputs on the display.

NEVER feel bad if you come to a point where you have to "sneak in" something to make it work the way the boss wants. As long as you keep things normalized, most DB actions will be so fast that nobody will recognize that anything complex happened at all.

I would like to apologize to everyone for my long radio silence. My mom has been in the hospital, and I haven't even looked at this since last Saturday.

I appreciate your comment. In my own defense, I relied on the owner's 40 years of experience to guide me on his assessment that we won't need more than 5 competing quotes per customer. I prepared it for 6 as a compromise.

I was trying to get the flow that he wanted to see, which is why I started down the path that I did, but there's no way I'm going to continue with my original construction after the strong opposition voiced by all of you. I really do respect your judgment, both individually and collectively, and I would be a fool to go against what you all so strongly advise. I have created a normalized structure.

Sort of.

I've created, and am using, a table with 5 fields. PKID, CVID, Company, Quote, and a bit field for lowest price. That will allow all of the data management that you've all helped me to realize wasn't present in the original structure.

However, the boss just didn't like the subform arrangement, so I did a little tweaking. I left the original field structure in place, even though I'm not using it for data management. And I used SQL expressions to add, update, or delete data in the new normalized table. That way I can have my cake and eat it too.

The main form is unchanged, but AfterUpdate runs SQL to move whatever data is entered on the form into the normalized table. It's obviously far more cumbersome to do it this way, but once it's dialed in it really does work exactly as I want it to, and it populates the table with the structured data needed for future use. Only the data in the normalized table will be looked at and considered. The existing fields only serve as placeholders and the jumping-off point.

I know this is kind of a Rube Goldberg arrangement that can not possibly satisfy the purists among you, but at the end of the day I have done exactly what you all recommended that I do. I've created the normalized structure to hold all the data that will be generated as we move forward. Only that normalized data will be considered in any reports or calculations.

I just didn't do it exactly the way everyone was expecting.

I'm looking forward to hearing everyone's reaction.

 

[MajP]

However, the boss just didn't like the subform arrangement, so I did a little tweaking. I left the original field structure in place, even though I'm not using it for data management. And I used SQL expressions to add, update, or delete data in the new normalized table. That way I can have my cake and eat it too.

If it is just aesthetics you can make the subform not look like a subform. Just play with the formatting. Here the 5 subform records look like main form controls. Using your colors.

 

[MajP]

I'm looking forward to hearing everyone's reaction.

I just presented to Access Europe today on this exact topic of Making "non - normal' forms while ensuring that you keep the data normalized. What you describe is similar to some of the techniques I demoed. Personally, in this case, I think I would have gone with the "fake subform" idea I show above. With enough formatting I think it would meet the requirements for display and interface and relies on all native forms and subforms and bound controls. You probably added some complexity where it is not needed, and lose some benefits. However, keeping the data properly normalized is way more important.

If you build a non standard form (like you did) there are no downstream effects. Any problems are at that form level. Lets assume you went with a completely unbound form. The worst thing is your code to read, display, delete, and save may need to get fixed if you did something wrong. Any possible problem is isolated to the form and can be fixed
If your table design is wrong that will have down stream affects. Future query, reports, code will require some janky work around and these can be cascading. People always focus on forms beginning, but a data base is meaningless if you cannot get information out. Often we are not thinking about future reports, queries, and other data pulls. If the data is normalized, any future query no matter how complex is doable. If not correct, then even a trivial data pull is difficult.

 

[bcmarshall]

If it is just aesthetics you can make the subform not look like a subform. Just play with the formatting. Here the 5 subform records look like main form controls. Using your colors.
View attachment 120438

I really like this approach. Can you explain how you did it?

 

[MajP]

I really like this approach. Can you explain how you did it

Just formatting.
Scroll bars - None
Record Selectors and Navigation bar - no
Name Field - locked and no tab stop. Might be disabled
Subform - Transparent border
Subform back color - same as main form

This will work fine if you do not have variable amounts of quote. In my example I allow you to modify and add additional quotes. Without scroll bars that would be hard to navigate and even know that there are additional ones, if it is beyond the size of the subform control. You could make the subform control a little larger and you would not know.
Or you could move it right a little and make it as big as you want and still put some fields below.

 

[MajP]

Form Aesthetics is not my strong suit, but I will say your example is just painful to look at. I would hate entering info into that.
With a little formatting and organizing you could make that nicer. Again this is not my strong suit there are people who can make really nice looking forms.
But I would do it more like this to allow me the option of adding additional quotes. This gives me some real estate to make the subform bigger than required.
Here is with the baseline 5. (Now that I count it is with six)

But if I need to add additional quotes the functionality is there.

That is nine so you can make the quote "frame" smaller if you think the absolute max is 6,7,8..
Also I would format "Company Name" different so it does not blend into the actual company names. Maybe underline or different color.