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

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.
 
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.
 
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?
 
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
 
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 :ROFLMAO: :ROFLMAO: But, I think you may have worn him down;)
 
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...
SQL:
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 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.
 
Final Answer:

SQL:
/*
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;
 
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;
 
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
 
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
 
1. Add customer
2.
names.PNG

3. If you select yes
changedefault.PNG


4. Modify Choices
modified.PNG

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.

add.PNG

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.
 

Attachments

Last edited:
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...
1750960818907.png

So this is simple.

The editing form is a whole different story:
1750960910428.png
1750961867521.png


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:

1750961215127.png

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.
 

Attachments

  • 1750961771040.png
    1750961771040.png
    28.9 KB · Views: 12
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.
 
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.
 
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.
 
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.
fakeform.PNG
 
Last edited:
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.
 

Users who are viewing this thread

Back
Top Bottom