I need a creative way to set control source

[bcmarshall]

I have an Access 2007database, SQL Server , 20 users for an insurance company that has been established for more than 4 years and is working well. There was a serious error made when it was originally set up. Each insurance company was assigned its own field with its own name as a place to store their rates.

This has become increasingly unwieldy as companies are added to or dropped from our roster, and I'm in the process of tearing the existing system out by the roots and replacing it with a system that will allow the companies to be drawn from a table.

This is a very complex description, but I think it's critical to understanding what my actual issue is.

I've selected an unusual approach to this, creating 16 fields in the database whose names are the numbers 1-16. Those fields are money datatypes, and will be where the competing premiums for different companies will be stored. This will allow me to minimize the number of fields employed and give me maximum flexibility.

We sell specialty insurance for toys, primarily RV's, boats, motorcycles, and some autos as well. Some companies may write policies for one vehicle type but not another, and by using the numbered field approach that same field can hold data for one insurance company for one vehicle type, and for another company for another vehicle type.

As a company is entered into the table, there are checkboxes to select the vehicle type that it sells policies for. The same company is assigned a different ordinal number (based on DMax+1) for each vehicle type it sells. Suppose that the same company is the third boat insurer entered, the fifth motorcycle insurer, and the eighth RV insurer. That company would them have the ordinal number of 3 for boats, 5 for motorcyles, and 8 for RV's. The values for those vehicles will be stored in fields 3, 5, and 8 respectively. Same company, three fields. It seems crazy, but in fact it's an efficient use of limited table field real estate. The computer can easily do a lookup of the ordinal for a given company based on vehicle type, and know which field to find the data in, so it's really not too extreme.

Now to get to the reason I'm writing.

I have a pop-up form that is used for recording competing company quotes. I've written a routine that counts the number of companies in the query and makes only that number of fields visible out of a total of 16. The problem is that I want to display the companies on that form in alphabetical order.

Each query that returns the companies for a given vehicle type has a sequential counter field in it which indicates the alphabetical order of the entries. It's another number field, but it's completely unrelated to the ordinal ID number.

Let's assume we have three companies for a given vehicle type, Progressive, Dairyland, and Foremost and give them the ID numbers of 8, 6, and 2. The alphabetical sequence for the same companies would be 3, 1, and 2.

Under this scenario, I want the control source of the field at the top to be the field called "6", but I can't assign that because suppose we add in BlueSky tomorrow. It now becomes the alphabetical leader, even if its ordinal ID is 9. For that reason, I'm searching for an expression that I can use to set the value of the control source depending on the sequential value returned by the query.

I tried DLookup("[Ordinal]","[RVIns]","[Seq]=1") (seq being the alphabetical first field), with and without an =, with and without enclosing it in Eval(), and the best I've accomplished is to fill in the field with the desired number as data, or a Name error.

How can I write an expression that Access will understand that will assign that control to a table field called "9" instead of a value of 9? The desired field is in the drop-down on the control source.

 

[Uncle Gizmo]

If I understand your question, (I skimmed it) I think you want to set a fields Bound property with VBA...

But if you carry on as you are, then you are digging yourself in to a deeper and deeper hole.

See this thread:- http://www.access-programmers.co.uk/forums/showthread.php?p=671226

 

[spikepl]

I've selected an unusual approach to this, creating 16 fields in the database whose names are the numbers 1-16. ... This will allow me to minimize the number of fields employed and give me maximum flexibility.

I concur with Uncle Gizmo. Your unusual approach will not give you maximum flexibility but maximum PITA.

Ennumerated fieldnames are almost always indicative of a structural problem. In your case, by doing it like so, you chuck away all the mechanism of SQL that would allow you to modify, calculate or compare values using the built-in tools and you condemn yourself to eternal PITA in terms of maintenace and laborious forms, queries and reports. Don't.

 

[bcmarshall]

Thanks for your advice, gents. I will rethink this whole structure based on your suggestions. The more I think about it the more I agree with you. The solution I was working on is probably going to come back and bite me in the ass down the road.

BTW, I was able to solve my original question.

I was looking for a method of setting the Control Source from within the field itself, but I realized that I can easily change the source externally using VBA or a macro and the expression I offered earlier. So I can get the source to vary based on alphabetical order on that form as I wanted.

However, I've got to look much more closely at what I'm doing with the overall structure. I'm not sure exactly what I want to do yet, but the numbered fields seem to be out.

You guys saved me from making a mistake.

Thanks.

 

[bcmarshall]

Maybe someone can suggest a better solution, but the main issue you need to be aware of is that I'm running out of fields, both in the main table, and also in the queries that feed the main customer forms. I have 9 fields remaining in the table, and even though obviously I can get more space by adding more tables, I can only feed a similar number of additional fields to the forms themselves, since they are also limited to 255 fields by their source queries and we are saving a ton of information on each record. I simply don't have room to keep adding insurance companies, and I don't want to take all the remaining fields to do so. That's the main reason I was considering the approach previously mentioned. It was awkward, but it did have the advantage of conserving critical fields for future development.

I already have a table where the insurance companies themselves are stored by name. I could easily assign each one a single unique ID number, and save that number in the main table that holds the customer record. Customer 1234 bought from or was quoted the best rate by company 1, while customer 1235 bought from or was quoted the best rate by company 12. That would actually free up a lot of fields currently assigned to individual companies, since only one would be needed, but I still need to be able to store the competing quotes. Cust. 1234 was quoted $100 by company A, $120 by company B, $140 by company C, etc. That's the information I'm trying to record with the structure I need to build.

If I take this approach, I'd need a new table whose fields have the insurance company names. Let's say there are 20 companies all told, so 20 fields, and there would be as many records as we have CustomerID's.

The main problem I face then is how to automatically create a new field in the new table that matches a new record I might add to the Insurance Companies table. Imagine that I add a new company, Joe's Insurance, and it's just another record in that table. Obviously no problem, but simultaneously I need to add Joe's as a field of that same name to the new rate-saving table in order to give me a place to store Joe's rates.

If I can accomplish that I can probably work this much more logically and effectively, but the need to actually add a new field to the rate-saving table each time a new company is added as a record in the insurance company table is a real problem. I want the office staff to do this just by adding a record, and the idea of having up to 255 insurance companies listed in the table is very appealing. I can both expand capabilities and save critical real estate at the same time, a real win-win situation.

All suggestions will be appreciated.

 

[Uncle Gizmo]

explain the rates table to me please.

 

[Uncle Gizmo]

I realise you're probably like me in that when you've got a problem you have to write it out verbose. However from the point of view of trying to come up with some help, it's uninviting to have to read through a load of text especially at 2 a.m. In the morning. I wondered if you might be persuaded to add a summary in the future.

 

[spikepl]

Agree with UG

Show some tables rather than a lot of words.

But skimming through your text you are still barking up the wrong creek, so to speak, paddle or no paddle.

You need to read up on the concept of data normalization. Google it. Data of same type all belongs in the same container - a column in your case, but with a second column to discern between the bits. Naming columns after specific instances of data is encoding information into your data structure, and that is a costly mistake to make. You keep all your money in one wallet in your pocket, not in separate wallets depending on intended usage.

For example, a price table of multiple suppliers/prices for goods would be structured like this:

tblPrices
-------------------
PriceID (PK, autonumber)
ItemID(FK, Long)
SupplierID (FK, Long)
Price (Currency)

this table can hold a price from any number of suppliers for any number of goods

Also, do not confuse display with storage - these two are entirely separate issues in Access (but not so in Excel) and can and should be dealt with separately

 

[Uncle Gizmo]

And I agree with spikepl and spikepl has explained it very well.

I also re-refer you to a link I posted here:- http://www.access-programmers.co.uk/forums/showpost.php?p=1359367&postcount=2

And my guess id that your rates table is "Storage of the same data in Fields" (Wrong) hence your difficulty.

So if you could explain your rates table we can start making headway...

 

[bcmarshall]

explain the rates table to me please.

This is a place to store the comparative rates of different companies. As I mentioned, For Customer 1234 Company A's rates were $100, Company B's rates were $120, Company C's rates were $140. There are many uses we put that data to. Each one is a currency column named with the name of every company in the companies list. We would have as many columns as companies, and as many records as customers.

If I could automatically create a new column on the SQL Server 2012 linked table as a company is added it would be perfect, but I've been unable to figure out how to do it.

I hunted online and found this code which works, but only for a local table.

Function AddField()
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fld As DAO.Field

Set db = Application.CurrentDb
Set td = db.TableDefs("CompetetiveQuotes")
Set fld = td.CreateField("NewField", dbLong)
With fld
.Attributes = .Attributes Or dbCurrency
'td.Fields.Append fld
End With
With td.Fields
.Append fld
.Refresh
End With

Set td = Nothing
Set db = Nothing
End Function

By the way, the code writer specifically mentioned the need to create a Long field and then rename it, so it's not a mistake.

The problem is that when I run it with the linked table I get a message that says, "Operation not supported on linked tables". I know the code is functional, and I suspect that the problem is in the line
Set db = Application.CurrentDb

You can't make any modification to a linked table from within Access, so the message makes perfect sense if I'm first directing it to the local db and then asking it to do a modification.

I interpret the message as saying "Operation not supported on linked tables from within a local db. If you want to modify a linked table you must direct the code specifically to it."

That's my best guess, anyway.

As always, all help will be appreciated.

 

[bcmarshall]

I realise you're probably like me in that when you've got a problem you have to write it out verbose. However from the point of view of trying to come up with some help, it's uninviting to have to read through a load of text especially at 2 a.m. In the morning. I wondered if you might be persuaded to add a summary in the future.

I really tried hard in the last post to be as brief as I could be and still remain understandable!

 

[bcmarshall]

Agree with UG

Naming columns after specific instances of data is encoding information into your data structure, and that is a costly mistake to make. You keep all your money in one wallet in your pocket, not in separate wallets depending on intended usage.

In general I agree with you, but this is a unique situation. I could have ten or more quotes from ten or more companies for one customer, and the logic (if you can call it that) behind naming the data fields the same as the company name is that we are in long-term relationships with these companies, and we often need to see and compare the historical data from different companies. In fact, this approach seems to be the easiest way to accomplish this, most especially if the new table has nothing but comparative rates stored in it, each grouped on a given CustID.

I'm still willing to listen to arguments against this approach that you may present, but potentially I can hold up to 255 companies in the table, far more than we'll ever use, and you may not realize that it's unlike a typical product because the prices vary by customer rather than product. Each customer has a different vehicle and different driving record, etc. so the rates are specific to that person from each company.

Imagine one customer ID as a record, and being able to scan across the line and see each company's quotation. That's what I'm trying to accomplish.

 

[CJ_London]

With respect, there is nothing unique about your situation. It would appear to be derived from an Excel solution.

Imagine the SQL required to find the lowest quote (I would think this would be a common requirement) or needing to order the 'list' in terms of value

Sounds like you need a table with a minimum of 4 columns:

CompanyID
CustomerID
QuoteID
QuoteValue

other columns perhaps around date, quote type etc

 

[bcmarshall]

With respect, there is nothing unique about your situation. It would appear to be derived from an Excel solution.

I thought long and hard about your comments before responding. You're absolutely right. What I have been proposing is an Excel solution.

Thank you for the reality check.

Let me ask something. We have a report called Competitive Pricing which is output in Excel format. It has demographic information about the customer and rates quoted for each customer. We use it to try to squeeze better rates out of competing companies. They can see what their competition is quoting for exactly the same coverage to the same customer, and it's a very important tool.

Using the design you have suggested, how would I create that Excel format in a report? Let's assume we have 10 companies, with ID's 1-10.

Because the format I've proposed is what you call Excel format, it's also extremely easy to output that way. I'm puzzling over how I could get the same report under the more appropriate format you've offered.

 

[CJ_London]

Databases should be designed for efficient storage and ease of use.

If you adopt the structure I am suggesting then in addition to that table you would also have other tables for companies, customers and quotes. but it does depend on the data you have and it's relevance.

For example, if customers never come back then you could incorporate their details in the quote table instead of having a separate one. But if they make repeated quote requests, it is better to store them in a sparate table, otherwise their details need to be repeated for each quote and if manually entered there is a risk you would lose the continuity of quotes by customer.

To create an view in a report where you want suppliers listed across the top you would use a Crosstab query which effectively pivots the data from a vertical view to a horizontal one.

My point about your current design is that although it might be easy to output as a straight view, any analysis or manipulation of the view (e.g.sort in order of value, averaging etc) becomes extremely difficult as does future changes/additions/deletions of companies.

 

[bcmarshall]

My point about your current design is that although it might be easy to output as a straight view, any analysis or manipulation of the view (e.g.sort in order of value, averaging etc) becomes extremely difficult as does future changes/additions/deletions of companies.

Currently we have nearly 100,000 records in our database. If I create one record per company per CustID , even if we have a low average of 5 companies quoted/customer, we'd still be looking at roughly half a million records in the new Rates table just to catch up with where we are now.

The company is only 4 years old and growing, and I could imagine that 4 years from now our total customer records in the database could grow from 100k to half a million. Following the same pattern of thought, the rates table would have a minimum of 5 records/CustID, or 2.5 million, and in some cases more than 5 companies are quoted, so the number could potentially even be higher.

My question is whether I'm digging myself into a hole with the number of records I'll be creating if I use the Rates table you describe, given that I'll be creating multiple rate records per customer record.

 

[Galaxiom]

Databases handle large numbers of records better than large numbers of fields.

In Access, the limit is the total file size not the number of records. I have had over nine million records in one table and it still worked fine until it hit the 2GB limit.

 

[bcmarshall]

Whoops I edited you post instead of quoting. I have almost done this so many times.

 

[Galaxiom]

This is a SQL Server based db, and I understand that the limit is 4 GB. Is that the limit in one table, or in the entire db?

If it is the full version of SQL Server the limit is 16TB for each data file and a total size of half an Exabyte so you wouldn't need to worry there.

The free version will do 4GB up to 2008. From 2008 R2 they go to 10GB.

Am I to interpret your response as saying that you concur with CJ_London that my best move is still to create one table where a separate record is created for each company even if it means making multiple records per CustomerID, potentially numbering into the millions?

Definitely. As a rule, database structure should avoid storing the same kind of information in different fields/columns.

Using different columns fo the same type of data means you are using the structure itself to encode information. The information belongs as values in records. So company needs to be indicated by a value in a field not the name of a column.

One general guiding principle that indicates you are going wrong is by looking at what is required to add a new piece of information. If you need to go into design mode, (which you would need to add a new company), then there is something wrong with your structure.

New data should always be able to be added by simply inserting records.

 

[bcmarshall]

New data should always be able to be added by simply inserting records.

Thank you all for the advice you've offered. I'm going to start transferring records to the new table.

BTW, am running SQL Server 2012.