Lookup fields v lookup tables

Greetings:

Recently I discovered a data type in Access 2007 I never noticed before. It is labeled "Lookup wizard." Exploring, I realized i could link a field to a lookup table in the table design view. How clever, I thought. There are two options: one is just to type in the values you want. I eschewed that because it did not look like it would document properly. The other is to specify an existing lookup table. That seemed safe. All the links appear correct in the relationships window and I can change or eliminate them.

It is very convenient because when I add such a field to a data entry form the response box comes with.

I understand that lookup fields are not good because they do not upload to SQL server and other reasons. But does the link to a separate lookup table with value ID and label have the same limitations?

If so, what is current good relational design, standard SQL practice, for getting response boxes to appear in forms?

If SQL is STANDARD query language, why would MS create something that is not standard, and is not even supported in MS SQL server? MS is sometimes hard to fathom.

Does the same problem occur with Y/N boxes? I have had some problems with Y/N boxes not appearing correct in UNION Queries. In the old days, a lookup table was required for Y/N items too.

Please advise.

David

Thanks. That is very helpful. Of course, business as usual.

My db will reside on SQL server.

I understand you to say never put the link to a look up or response box table in the table design.

I infer that the recommended approach is to go into the relationships view and set the link between the field and the look up table there and not in the table design. Do I have that correct? When I set that relationship the "combo box" still appears automatically in the form. Is that right too?

There has been a proliferation of data types. In the old days we used 0,1 for yes/no, male/female etc. I am not sure what the advantage of 0,-1 is. Nevertheless, I think I can still put a check box on a form no matter what the underlying values of the lookup table are. Is that correct?

David

When I set that relationship the "combo box" still appears automatically in the form. Is that right too?

Click to expand...

No it won't. You will have to construct it yourself.

BTW, Lookup as data type in Tables was in 2002 also. If I do decide to use it, when using that field in a report, I use Change to... to make it a Text Box. It is annoying in Datasheet view (of the table) because it displays the pull down box which wastes space.

wilpeter said:

BTW, Lookup as data type in Tables was in 2002 also. If I do decide to use it, when using that field in a report, I use Change to... to make it a Text Box. It is annoying in Datasheet view (of the table) because it displays the pull down box which wastes space.

Click to expand...

Not quite correct.

There is no such thing as LookUP for a DataType.

What there is in 2002/2003 is a LookUP Wizard.

But don't use it. If you do use it you will be wondering why things don't work the way you expected.

Thanks. It boggles the mind that MS would include that. I just stumbled on to it. I am old school and usually do not look that far down the list of data types. I am busily taking them out of my tables.

Greetings:

It is as easy to add a bound combo box to a form as it is to link a lookup table to a field in table design. I am not sure This is about "helping" novices. As one responder indicated, it seems more likely MS is trying to sell Sharepoint and with that to drive changes in SQL itself. You create the same link in a form or report, so MS could have opted to define the link in the table design and have it available for forms and reports but not alter the table design so that it interfers with uploading to servers. Just a thought.

I want to clarify two things:

First, are Y/N data type fields subject to the same limitations as lookup tables defined in the table design? The only issue I have seen with Y/N fields is that the check box does not appear in UNION queries (only the 0, -1 values), although it does in all other types of queries. The Y/N check box can be added easily to a form or a report as a bound control. Is this always the best practice?

Second, Date/Time fields also have formatting in the table design. For example, by default, the date-picker appears in the data sheet view of tables where date/time fields are used. Does this have the same limitations as defining links to lookup tables in the table design?

I can use the activeX control for dates to create a date-picker that adds the correctly formatted date in a text field, but it is still possible to enter an improperly formatted date in that text field if the user does not use the picker. What about time of day fields? In addition, the date-picker is present on the form in expanded view rather than as the date-picker icon in the right hand corner of the response box.

Please advise.

Now that's a post that should be saved. Thanks for the insight, Pat. One tends to overlook that, years after the design, the user/customer will likely upgrade his operating system and thereby MS Office version.
In 4., you mention that "...regarding date fields, I never use an input mask." Do you feel the same way about them for other types of field (phone, SSN, etc.)?

Greetings:

I have used Access since A97, when I left dBase, but I never noticed the lookup wizard as a data type before. I have been reading a lot about various wizards lately (never used them much) and when lookup wizard caught my attention I tried it. Usually, I have done queries on the backend of databases for research purposes. I have not done a lot with forms or formal reports until now. Now, my clients need to have full apps in order to have some data to analyse in a some key overlooked areas and for horizontal intergration, so here I am becoming a developer in order to provide them the data to analyse, and to link all their various apps together. Back in 98 I consulted with a CDC Aids hotline contractor who wanted to have a single query that would link payroll, HR, and telephone survey data. Then it was virtually impossible. Many orgs still cannot do this. Your insights have been very helpful.

Re: 2) Adding the check boxes to forms and reports is not a problem. I never quite understood why people would use 0 and -1 as the values rather than 0 and 1, as in the old days. Now I understand that it is the pre-programmed check box control that assigns those values in data entry forms.

Suppose I had an item on a form with responses like strongly agree to strongly disagree. I want the user to see the text but I want the numerical values entered in the table. How would I do that?

Re3: I lose you a little here. If I understand you right, you are saying to leave the fomat property at the default setting (blank) in the table design, and change it or specify it in the form or report. Is that right? But the default value in the table design is the short date. Are you saying that if you leave that format property blank in the table design you can then reset anyway you wish in forms and reports? But that if you select a format in the table design you cannot change it in the forms or reports?

Re4: Thanks for the advice about the active X controls. The input mask is where you, for example, specify the dashes in a SSN or phone #. Do I have that right? Or is it where, for example, the SSN field must have 9 digits to go forward? I am still a little confused here. I also do not know how to use unbound controls yet.

Thanks:

Something unexpected has happened.

I first went to the relationships page and deleted all the relationships to look up tables. Then I went into the table design view and changed the data types to text. I also changed the data type for all Y/N fields to long integer.

Finally, I opened the forms to add the combo boxes and the check boxes. But they were all still there, except in places where I actually deleted the original field and replaced it with a new field. And they all still work. No check boxes appear in the datasheet view of tables, only the numeric values. Only the correct text displays in the fields that had lookup tables.

So, have I solved the problem or is Access somehow still remembering things I have deleted?

When I look at the field list in the design view of the forms, the linked tables still appear associated with the fields even though I deleted the link and changed the data type.

I fear that deleting the links ad changing the data types is not enough to resolve the issue.

Read the second Commandment Of Access.
http://access.mvps.org/access/tencommandments.htm