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

[MajP]

The solution to keep the data normalized, provide the functionality you want, and more is so simple, it is just dumb not to do it.

Lets assume at any give time you are going to want 5 to 8 default quotes. This may change periodically
1. In the Company table add a field so that you can identify which ones are required by default
2. You would have a form to open this table and change the defaults when needed.

In this case I tagged the first 5, but the beauty in this design it can change whenever you want it. And if you want more or less that can change.

3. Now when I enter a new lead I am going to require the defaults when I create a new lead. If I change the defaults or add to the list I get a new set of defaults.

4. When I create a new lead I do something like this, and have to add a user name since a lead without a name does not make sense.

5. I add a name and Voila, the default quotes are ready to be filled in

For some reason you adverse to subforms although that is pretty standard in any database. However with a little formatting abouve like removing record selectors, removing border, removing colored header you would not know the five records are on a subform.

6. Each time I add a quote conditional formatting will show the lowest quote

I did not do it because you are hung up on just 5, but at this point my design has the flexibility to now add more quotes, delete quotes, replace quotes.

This is as easy as what you have
Way more flexible
Supports all future capabilities and data analysis.

Am I missing any requirements or functionality?

 

[MajP]

BTW the boss would like to know the Min, Max, Average quote for the last year by company and how many we have.

Company_Name	Avg Quote	Max Quote	Min Quote	Number of Quotes
Geico	$641.25	$999.00	$123.00	4
Liberty Mutual	$595.00	$888.00	$323.00	5
National General	$511.20	$789.00	$123.00	5
Progressive	$505.00	$666.00	$200.00	4
Safeco	$399.33	$777.00	$100.00	3

That took me ten seconds because my data is normal.
Don't even try to come back and ask how to do this with your data. Requires a normalizing query or and/or lots of vba. If the defaults would change over time or you allow additional quotes then this above question is trivial in my design and a near impossible in your design.
And that is why we could care less that you got one form to kind of work, for now, with a hope that nothing changes and no new requirements.

 

[MadPiet]

You have my permission to use the quote, not that you needed it. But thanks for asking.

I did not come to this forum, hat in hand, seeking help from a bunch of caring strangers with common interests, with the intention of just ignoring what was so sincerely offered. You guys are superstars, and I really mean that.

I charted my own path after giving serious consideration to your collective input. I hope I made the right call.

Maybe post your design. Just an empty database. The thing I'm afraid of, having worked with messes like this, is that answering a simple question with a database that isn't designed correctly can be really hard. When I used to do this with databases I inherited, I'd map out the limitations of the current database design. The one thing I'd be afraid of is painting myself into a corner with a bad design.

 

[bcmarshall]

I spent 20 years in the insurance business myself, both outside as an agent and inside as an employee.

Your client may need 5 companies at any given time.

He will end up replacing one of them within the very near future, and probably more than one. That's when this decision will come back to haunt you, not him. Because you are building in future billable work for yourself that he will have to pay for.

You even laid the groundwork for that to happen: "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."

I apologize that we can't convince you. I apologize in advance for the hard times to come.

I know you're absolutely sincere in your warning, and I do appreciate that, but please consider the following.

Worst case nightmare scenario is that after we have 10k or 50k records in the system, one of the three major companies named in the fields goes out of business, or, more likely, there's a falling out between our agency and the corporation. For whatever reason, we decide to end the relationship. Now what do I do?

If I got this news tomorrow I'd have it up and running in 20 minutes best case, 1 hour on the outside. I'd simply create a new column in the table, name it for the replacement company, and replace the control source and labeling for the control on the form to direct it to the new named field. The data related to that company would remain available, and all of their associated sales data would remain intact.

Please remember that we consider this to be an extremely unlikely scenario, and surely would not happen more than once given the size and stature of the named company fields. Only giant companies have named fields.

If it happened to be one of the 8 smaller companies I've listed in the table, no change would be needed at all. I'd just add the replacement company to the table, remove the one we're no longer working with, and go on with life.

If the owner decided to elevate one of the companies now in the table to be one of the three, I'd create a new named field for the elevated company, and run an update query to transfer all the business linked to it while it was listed as Company1, Company2, or Company3 to the new named field. Then I'd delete the data related to that company that's held in the generic fields so that everything would remain unchanged.

I know that what I've suggested is far more complicated than it would be if I went to a strictly normalized system, but I also think in this one limited instance my solution is the most reasonable when taking everything the company is considering into account.

 

[MajP]

f I got this news tomorrow I'd have it up and running in 20 minutes best case, 1 hour on the outside. I'd simply create a new column in the table, name it for the replacement company, and replace the control source and labeling for the control on the form to direct it to the new named field. The data related to that company would remain available, and all of their associated sales data would remain intact.

Please remember that we consider this to be an extremely unlikely scenario, and surely would not happen more than once given the size and stature of the named company fields. Only giant companies have named fields.

If it happened to be one of the 8 smaller companies I've listed in the table, no change would be needed at all. I'd just add the replacement company to the table, remove the one we're no longer working with, and go on with life.

If the owner decided to elevate one of the companies now in the table to be one of the three, I'd create a new named field for the elevated company, and run an update query to transfer all the business linked to it while it was listed as Company1, Company2, or Company3 to the new named field. Then I'd delete the data related to that company that's held in the generic fields so that everything would remain unchanged.

But I showed you that you can have your cake and eat it too. So why not? I get you did not want a traditional subform and force the user to add each company, but that has been handled.
All those problems above go away. Data entry is no different. You can make your form look like and do exactly what you want and more. You support future requirements. You can change the default companies with a checkbox. Support future reporting, summation, and analysis.
There is a little bit of up front code, but everything falls in place after that.

 

[bcmarshall]

BTW the boss would like to know the Min, Max, Average quote for the last year by company and how many we have.

Company_Name	Avg Quote	Max Quote	Min Quote	Number of Quotes
Geico	$641.25	$999.00	$123.00	4
Liberty Mutual	$595.00	$888.00	$323.00	5
National General	$511.20	$789.00	$123.00	5
Progressive	$505.00	$666.00	$200.00	4
Safeco	$399.33	$777.00	$100.00	3

That took me ten seconds because my data is normal.
Don't even try to come back and ask how to do this with your data. Requires a normalizing query or and/or lots of vba. If the defaults would change over time or you allow additional quotes then this above question is trivial in my design and a near impossible in your design.
And that is why we could care less that you got one form to kind of work, for now, with a hope that nothing changes and no new requirements.

Although it may appear that way, I assure you that your collective comments have not fallen on deaf ears. I've been listening carefully.

These conversations have made me realize that right now my development is technically a hybrid. I have quasi-normalized data in the three generic fields whose data is drawn from a table (Company1, 2, 3), and three fields that are not normalized. I say quasi-normalized because it's currently three company fields rather than one, but it is still in a normalized format.

I'd like to ask a different question.

Suppose I replaced the three named fields with three drawn from the table as the others are, (Company4, 5, 6). I can default value the controls to the names I want, and yet I'll have the ability to change everything in an instant.

In this scenario we don't have one company column and one quote amount column. We have six but they can all be combined via query, so I can still have what amounts to a normalized database.

I kinda like this solution in that it takes into account what everyone has been saying but still offers the entry simplicity that we're seeking.

Wondering what your reaction is to that? Is it better, worse, or lipstick on a pig?

 

[GPGeorge]

Although it may appear that way, I assure you that your collective comments have not fallen on deaf ears. I've been listening carefully.

These conversations have made me realize that right now my development is technically a hybrid. I have quasi-normalized data in the three generic fields whose data is drawn from a table (Company1, 2, 3), and three fields that are not normalized. I say quasi-normalized because it's currently three company fields rather than one, but it is still in a normalized format.

I'd like to ask a different question.

Suppose I replaced the three named fields with three drawn from the table as the others are, (Company4, 5, 6). I can default value the controls to the names I want, and yet I'll have the ability to change everything in an instant.

In this scenario we don't have one company column and one quote amount column. We have six but they can all be combined via query, so I can still have what amounts to a normalized database.

I kinda like this solution in that it takes into account what everyone has been saying but still offers the entry simplicity that we're seeking.

Wondering what your reaction is to that? Is it better, worse, or lipstick on a pig?

TRY what MajP has been offering. It's the perfect compromise.

Data is either normalized or it is not normalized. Please disabuse yourself of that "quasi-normalized" misunderstanding.

 

[MadPiet]

Since I don't know your exact structure, but I've dealt with this before, I'll use my example. A long time ago, I inherited a database where my job was basically to do frequency counts of symptoms and grades. (like the drug ads on TV that say "common side effects include mild [symptom]...")
What I inherited was structured like this:
CREATE TABLE SymptomReport(PatientID INT, Cycle TINYINT, Alopecia, Causality1, Relatedness1, Anemia, Causality2, Relatedness2...)

Might save a tiny bit of time entering data, but getting minimums and maximums across a single record would be a nightmare. I had to create a hideous view that unioned all the stuff together like this:

SELECT PatientID, Cycle, 'Alopecia' AS Symptom, [Alopecia] AS Grade, Causality1 AS Causality, Relatedness1 AS Relatedness
FROM SymptomReport
UNION ALL
SELECT PatientID, Cycle, 'Anemia' AS Symptom, [Anemia], Causality2, Relatedness2
FROM SymptomReport
UNION ALL
...

I can't even tell you how many databases I've had to fix that aren't normalized. Eventually, you're going to be asked questions that require a properly normalized structure and unless you're really good at queries, it's going to be either a huge pain or impossible to answer. I would explain that part to them. Basically, by not normalizing, you're cutting yourself off from "Easy questions have easy answers.", but since you're the one that's going to have to do the answering, that's on you. (Oh, and the person that designed all the databases like the one I was describing... yeah, she got fired after I pointed out that her designs were the root of their problem. When I got there, it took like 2 weeks to summarize a study. When I left, I could do it in less than a minute... so there's that.
then I could summarize that union query... slow, but it works.

 

[GPGeorge]

Although it may appear that way, I assure you that your collective comments have not fallen on deaf ears. I've been listening carefully.

These conversations have made me realize that right now my development is technically a hybrid. I have quasi-normalized data in the three generic fields whose data is drawn from a table (Company1, 2, 3), and three fields that are not normalized. I say quasi-normalized because it's currently three company fields rather than one, but it is still in a normalized format.

I'd like to ask a different question.

Suppose I replaced the three named fields with three drawn from the table as the others are, (Company4, 5, 6). I can default value the controls to the names I want, and yet I'll have the ability to change everything in an instant.

In this scenario we don't have one company column and one quote amount column. We have six but they can all be combined via query, so I can still have what amounts to a normalized database.

I kinda like this solution in that it takes into account what everyone has been saying but still offers the entry simplicity that we're seeking.

Wondering what your reaction is to that? Is it better, worse, or lipstick on a pig?

Does it matter to your client whether they can create a report summarizing the business they've done with every company for each one of the prior 10 years, by year or aggregated?

If that report would matter, you have no choice but to abandon the non-normalized approach.

 

[MajP]

Suppose I replaced the three named fields with three drawn from the table as the others are, (Company4, 5, 6). I can default value the controls to the names I want, and yet I'll have the ability to change everything in an instant.

In this scenario we don't have one company column and one quote amount column. We have six but they can all be combined via query, so I can still have what amounts to a normalized database.

I kinda like this solution in that it takes into account what everyone has been saying but still offers the entry simplicity that we're seeking.

Wondering what your reaction is to that? Is it better, worse, or lipstick on a pig

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.

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.

 

[bcmarshall]

The solution to keep the data normalized, provide the functionality you want, and more is so simple, it is just dumb not to do it.

Lets assume at any give time you are going to want 5 to 8 default quotes. This may change periodically
1. In the Company table add a field so that you can identify which ones are required by default
2. You would have a form to open this table and change the defaults when needed.

View attachment 120356

In this case I tagged the first 5, but the beauty in this design it can change whenever you want it. And if you want more or less that can change.

3. Now when I enter a new lead I am going to require the defaults when I create a new lead. If I change the defaults or add to the list I get a new set of defaults.

4. When I create a new lead I do something like this, and have to add a user name since a lead without a name does not make sense.

View attachment 120357

5. I add a name and Voila, the default quotes are ready to be filled in
View attachment 120358
For some reason you adverse to subforms although that is pretty standard in any database. However with a little formatting abouve like removing record selectors, removing border, removing colored header you would not know the five records are on a subform.

6. Each time I add a quote conditional formatting will show the lowest quote
View attachment 120359
I did not do it because you are hung up on just 5, but at this point my design has the flexibility to now add more quotes, delete quotes, replace quotes.

This is as easy as what you have
Way more flexible
Supports all future capabilities and data analysis.

Am I missing any requirements or functionality?

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

I'm gonna make the changes you've suggested. I can't tell you all how much I really do appreciate the things you've done for me.

 

[MadPiet]

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

 

[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