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

[RECrerar]

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?

 

[Rabbie]

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.

 

[RECrerar]

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?

 

[Rabbie]

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?

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.

 

[RECrerar]

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.

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

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

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.

 

[RECrerar]

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.

 

[RECrerar]

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