Are lookup fields okay if they are just typed values not from a table?

Hi,

The question is really in the title.

I was having a couple of issues with my database and so did some searching and found all the information about how bad lookup fields are in tables when they are looking up data from other tables.

I am therefore taking all of these out of my database. Some of the lookup fields are not from tables but just a typed list of 2 - 4 values in the table.

Would it be better pracitcis to create very small tables containing these values and get the information that way or in this case is it okay to keep teh lookup fields?

The problem with look-ups in tables is that they are misleading. You see the look-up value not what is actually in the table.

It is perfectly OK to have look-ups in forms etc just not in the actual table.

Tables should never be seen by users. They should just be datastores so you don't need to hide what is actually there.

Yeah that's what I've realised hense why I am taking out all off the lookups that were in tables. However for a few of the fields there are only 3 possible values, (eg Yes, No, Regulated). For these would it still be better to have them in tables rather than a lookup in the main table?

RECrerar said:

Yeah that's what I've realised hense why I am taking out all off the lookups that were in tables. However for a few of the fields there are only 3 possible values, (eg Yes, No, Regulated). For these would it still be better to have them in tables rather than a lookup in the main table?

Click to expand...

I wouldn't have them as look-ups in the tables I would either store them in the table as text strings ie "Yes", "No" "Regulated" etc or store them as numbers ie 1=Yes, 2= No, 3= Regulated. The second way is I think better because it uses lesssstorage space and can be cross referenced to separate look-up table where you can pick up the value for use in forms/reports etc.

Don't confuse look-up tables with look-up fields in a table.

I wouldn't have them as look-ups in the tables I would either store them in the table as text strings ie "Yes", "No" "Regulated" etc or store them as numbers ie 1=Yes, 2= No, 3= Regulated. The second way is I think better because it uses lesssstorage space and can be cross referenced to separate look-up table where you can pick up the value for use in forms/reports etc.

Click to expand...

OKay, I think I will go with the second method, which is what I currently am doing for the majority of the fields.


In my minds I don't think I am, hope the post wasn't too confusing.

Thanks for the help. I really wish I'd realised this sooner, this is going to take some time to put right, but it should be much better in the end and guess stuff like this is what happens when you're learning as you go along.

Rather than having a separate table for each lookup field, I use a two-table scheme that holds ALL simple lookup values. One table is a table of "tables" and the second is the values for each table. For example
tblTables:
TableID (autonumber PK)
TableName

tblCodeValues:
CodeID (autonumber PK)
TableID (FK to tblTables)
CodeNameLong
CodeNameShort
SeqNum
InactiveFlg

This allows me to have long and short versions of the code values so I can choose based on the space available in reports or forms. The sequence number is used in case the user wants to specify an order for the dropdown code list. And the Inactive flag allows me to keep old code values in the table but programmatically prevent them from being assigned to new records.

I also have a couple of standard forms and reports to go along with the code tables.

And finally, I create a lookup query for every code table so that I can use that query to populate dropdown lists. This saves me the trouble of having to remember or look up the tableIDs for each code set as I am creating forms.

I import them as a set into each new application which gives me one less thing I need to think about. I only create stant-alone code tables when the code list is hierarchial or more complex than than what will fit into my streamlined design.

PS, although I don't use embedded code lists, you will not get into any trouble using these at the table level. The problem is that with split databases, code changes will not be propagated to your forms and queries so you will have to propagate all changes manually. That tips the scales in my book to sticking entirely with code tables.

Hi Pat,

That is certainly a very appealing option as I currently have about 12 small tables that could easily fit that format. A couple of questions and possible concerns though.

for the sake of these questions I'll use my table name. Basically most of these little lookups relate to field in the main table called tblMainData

1. I can see how you use your table to get the drop down lists in forms. What data would actually be stored in the main table tblMainData in my case? Oh actually, don't worrk about that, I assuem you then store the Primary Key if tblCodeValues in the main table.

3. Is there a defined relationship between the tblCodevalues PK and the tblMainData fields that use the table or is that all just done with lookups?

2. This is really an "is it worth it" question. Basically I have about a month to get this database working and I'm not likely to be making another one for a while. Putting in these changes are going to require major redesigns of the forms and reports that I already have which will be very time consuming. However there is still a fair amount that I have to do including putting a form together that will allow anaysis of any numerical field (mainly financial stuff) against any text field and I think the neater design would make this easier and I assume someone else is going to have to maintain the database after it's finished so the simpler it is the better.

I think I've really just answered my own question that it would be worth doing. Phew, time to get a tea and settle in for the night.

Hi Again Pat,

Just wanted to say your table of tables and code values is awesome. Am really liking it now, it makes everything so much more streamlined and easily understandable.

Thanks so much, Robyn

Glad you decided to use it. It is such a simple concept and I have been using it in various incarnations as far back as the early 80's with COBOL and IMS.
1. Yes. The PK of the Code Values table.
3. Yes. I define RI in all cases. You just need to add the code Values table to the relationship window, once for each relationship in a single table. There is a Jet limit of 35 relationships to a single table that I only ran afoul of once in all the years I have been using this. Luckily, the app was destined to be upsized to SQL server where I didn't have a limit issue.
2. If you have used queries as your rowsources, it won't be too time consuming. Just change the queries.