I recently broke Access 2010 by creating trying to change a multiple column value list combo box to a single column value list combo box. Access claimed the change was irreversible and would not even let me change the datatype. I could not even exit the design view or exit Access itself, I had to manually end the process. So that leads to to wonder if value lists are even wise to use. It was suggested to me in a smaller forum that Access does not handle them well and Access can break the object and the whole field, or even table may have to be recreated.
When should one use a value list instead of a table lookup in general?
I don't mind creating many small single column tables, I just want to do the database right. And since I have not done this before, and have had no formal instruction besides some books I have purchased, I learn better by practicing
In my situation, I am working on client information, where I will have a handful of table fields where I will want specific value, but the values to choose from will be very small in number. 2 at the minimum and maybe 10 at the max for maybe 3-5 fields. (being new to access, I am realizing the potential of relational databases so I can't predict how many such fields what I will actually use).
Example fields will be customer status (current, cancelled, pending, ext. vacation...), price (which may have to be manually changed when an odd price is mistakenly quoted, but are normally in $5 increments in a small range). It would seem to me that the best practice is to create tables, and that value lists are shortcuts when the designer only has a small list of values.