To Lookup or not to Lookup...that is the question! (Apologies to the bard...) can someone please help me in my efforts in designing a new database?

Users will be tracked on standard questions which are in the form of the observations of safety practices. The observations are somewhat consistent but may change at anytime or additional ones added, such as "proper safety planning conducted?", "eye/face protection used?", "hearing protection used?"..... but the response for each can only be one of three values - Safe, Unsafe or N/A.

What is the best way to tackle this? Should I set up a table of the three 'standard' observed values, and use this as a lookup for the table of observations for a field called observed value?

Or...should I create a combobox within a form for inputting observations such as mentioned in the article "The Evils of Lookup Fields in Tables"?

My other goal here is to show all three options - of the observed value - everytime an observation is presented in the Input Form.

This post has been edited by PA_Callaghan99: Mar 27 2012, 01:33 PM

Depends on the quantity of information and "future proofing" you are wanting to build into it. If this is going to be a short term data collection (<1year) then I wouldn't bother normalizing and just get it out as fast as possible. If it is going to be used for years with multiple hands "in the pot" then you will kick yourself for not having normalized at the beginning (IMG:style_emoticons/default/wink.gif)

PA_Callaghan99,

(IMG:style_emoticons/default/welcome2UA.gif)

Your question is a little vague, but I'm assuming you're referring to table fields that have a combobox in them, looking up displayed values from another table. (For example, in a table where you have timecards stored, you have a field to store which Employee is related to the timecard data in each record. The Employee field could be set up to look up employee names from the EmployeeList table.

Personally, I avoid this like the plague. (IMG:style_emoticons/default/smile.gif) Even though Access allows this (and Microsoft even includes such data in their demo databases), it's not a good idea. I believe that end users should NEVER interact with table data directly - they should see forms and reports only. Let a combobox on a form be the way the user selects an employee, but in the table, store a Long Integer value that is linked to an Autonumbered Primary Key field in the EmployeeList table instead.

Click here for a more in-depth analysis.

Hope this helps,

Dennis

Hi

I would agree with Dennis

pmfji:

Lookup fields in tables are a lot like pouring yourself a cup of sugar and spooning a bit of coffee on top.

That first sip is REALLY sweet, but it doesn't take long to realize this is going to be very bad for you.

ALL data entry should go through forms. Put your lookups in combo boxes in forms.

Don't tempt fate.

Is there anything wrong with creating the lookup field in the table, creating the form and then going back to the table design and changing the field back to text box? This way the combo box is created automatically on the form.

mktw,

I wouldn't say there's anything "wrong" with that strategy - it's just a lot more work. It takes a lot less time to create the combobox on the form using the wizard than it would to create a combobox in a table manually.

Hope this helps,

Dennis

Welcome to UtterAccess.

As a matter of fact, I know some well seasoned developers follow a similar approach early on in development, especially when working on table design.

The problem what that is, as I see it, people who understand good database design and who know how to avoid problems can safely use lookup fields that way during initial schema design.

However, people who are more likely to make bad mistakes in table design are also more likely to be misled into relying on them without understanding the pitfalls they introduce.