Lookup fields v lookup tables (1 Viewer)

[dscudder]

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

 

[Pat Hartman]

Lookup tables and combos are wonderful and useful items, just NOT in tables. There are serious issues with lookups at the table level once you try to make your own queries or use them in VBA. I suggest removing them immediately.

The bad things that MS has done to Access beginning with A2007 are directly related to their push to get everyone to use SharePoint. They are trying to make Access more integrated with Sharepoint and every step they take in that direction causes them to loose developer advocates because developers will not switch to macros no matter how MS pushes them and folks who understand relational databases would never use the non-normalized data-types, etc. MS has the misguided impression that professionals don't use Access and so with each new version they make it stupider in an attempt to makie it easier for non-developers to use.

Y/N datatypes don't upsize well because SQL Server doesn't support null values. I rarely use them myself because I frequently use SQL Server back ends. You can define integer data types that support -1, 0, and null and use checkboxes to display them. So, you get the benefit without the hassle.

 

[dscudder]

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

 

[RainLover]

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

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

 

[wilpeter]

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.

 

[RainLover]

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.

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.

 

[Pat Hartman]

Relationships are defined in the back end and they are used to enforce referential integrity. Lookups are done on forms by using combo or list boxes. For reports, use a RecordSource query that joins the main table to the lookup table so you can select the "text" value for the report. Combos don't look nice on reports so you want to stick with text boxes but you want the text box to display the lookup value rather than the foreign key hence the join to the lookup table.

Defining lookups in a table looks convenient. It sure makes it easy when you open tables directly. However, when developing for others, you would never expose tables to them. You would always use forms. Again, having the lookup defined on the table is convenient because when you create a form, Access knows you want a combo for the lookup field. Here is where the convenience and "goodness" ends and it ends with a crash. How many forms will you be defining for any given table? Probably not more than one so how inconvenient is it to change a textbox to a combo that one time? You will probably create more reports and in this case it is inconvenient to have Access insist on creating a combo because on reports, you will want text boxes. And then there's VBA. Don't try to write code that manipulates a recordset based on a table with a lookup field. you'll never be able to figure out when the field is numeric or when it is text according to Access. Your sorts will be "funny" and won't be what you expect, and the list goes on and on.

 

[dscudder]

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.

 

[Pat Hartman]

Many bad things in Access are put there to "help" novices. This is one of those really bad things.

 

[dscudder]

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.

 

[Pat Hartman]

1. I would agree with the driving Sharepoint comment except that lookups at the table level have been around at least since A97 and I'm pretty sure they go all the way back to 2.0 and possibly earlier.
2. Y/N data types work well enough with Jet/ACE because they both allow a third value which is null. SQL server has implemented the datatype more precisely and only allow two states. Since most of my apps use SQL Server (or could in the future), I use the integer data type. The only inconvenience is that I have to add the checkboxes to forms/reports myself. Access doesn't do it automatically. Union queries have some less than interesting quirks and this is one of them. No one should be looking at the queries anyway so just make sure the forms show checkboxes.
3. Some. Using the Format property does not change how dates are stored. I think this setting is just used to pre-define the date setting for the control on a form or report. It also affects how the data is displayed in queries so I actually never set the format here. In some date fields I also want to store time and setting the format to short date for example, will prevent the field from ever showing time unless I actually click into the field. Setting the format at the table level limits your options and since no user ever gets to look at tables, there is no reason to set the property here.
4. Not if you change the data type of the control by setting its Format property to short date or some other valid date type. You will need to get rid of the ActiveX pickers when you start using A2010. They are still supported in A2007 but A2007 has its own native picker that you should use instead. All you have to do is set the Format of an unbound control to one of the date formats and the picker appears when you click the field. In bound fields, the form will recognize the datatype of the bound field so you don't have to set the Format to get the picker. Also regarding date fields, I never use an input mask. I find them extremely annoying and they require more typing so I just let my users type freeform. As long as the control knows it is a data data type, it won't let the user out of the control if the date format is invalid. I enter dates as just d/m and Access fills in the year. You only have to enter the year if you don't want it to default to the current year. So, if you're entering data for 2011, you need to enter year also - 1/1/11 will do it whereas with the input mask, you'd have to type 01/01/2011.

 

[wilpeter]

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.)?

 

[dscudder]

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.

 

[Pat Hartman]

Wilpeter,
The other input masks are OK. It is just the date one that I avoid. I'd rather type fewer characters and let Access fill the rest.

dscudder,
When you have multiple posibilities, use a combobox or an option group. The option group is less flexible but more visually apealing when you have only a couple of choices.

You can override the date format in forms and reports. I just don't set it for tables because, I don't want to surpress time values when they are present.

The input mask is used to guide the user when typing and to ensure that he types letters or numbers as appropriate.

 

[dscudder]

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?

 

[dscudder]

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.

 

[Pat Hartman]

The lookups are not defined in the relationship window. They are defined in the table definition. You need to open each table in design view. Select the colunn you want to change and then change the lookup from combo to textbox. You can also change the lookup from textbox to chkbox for the fields that were previously defined as yes/no.

DO NOT change the field types. DO NOT delete the relationships. You may have to change the combos on forms to specify the correct table/query as the RowSource. You won't have to change the chkbox controls. They will be fine bound to integers.

 

[eirman]

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