Tables - without Lookups

[flebber]

Whilst trying to develop my access db I have been reading a lot of different information and advice. Some conflicting.

In Matthew MacDonalds book Access the missing manual he advises on using Lookup's in tables. So that when entering information into tables and you don't know an ID number that is linked from another table you can use the lookup to have the information available rather than jumping back and forth between tables. Starts from Chapter 5 of his book on access 2007.

Then I had a link to http://mvps.org/access regarding a time query I had. However I found a lot of information on the site so got stuck reading and came across; "The Evils of Lookup Fields in Tables" which outlined the following. From http://mvps.org/access/lookupfields.htm

The Evils of Lookup Fields in Tables​

Contributors

Arvin Meyer Joan Wild

1. A Lookup field in a table displays the looked-up value. For instance, if a user opens a table datasheet and sees a column of company names, what is in the table is, in fact, a numeric CompanyID, and the table is linked with a select statement to the company table by that ID.
2. Any query that uses that lookup field to sort by that company name won't work. Nor will a query that uses a company name in that field as a criteria. If a user creates a combobox to select the company using a value list, the data in the table can be over-written.
3. Another relationship is created which then creates another set of indexes when a Lookup field is created, thus bloating the database unnecessarily.
4. If a combobox based on the lookup is used in a form, and a filter is applied, the persistent filter effect of Access often saves the filter and the next time the form is opened, there will be a prompt for the value (which cannot be provided, thus creating an error).
5. Reports based on the lookup field need a combobox to display the data, causing them to run more slowly. The underlying recordsource can also be modified to include the table, however the index, (unless it was set up within a proper relationship) may not be optimized.
6. Lookup fields mask what is really happening, and hide good relational methodology from the user.
7. The database cannot be properly upsized to, or queried by, another engine (without removing all the lookup fields) because no other engines use or understand them.
8. If security is implemented, permissions to tables is usually denied, and RWOP queries are used for data access. There will often be errors that there are no permissions on a specific table that isn't even being used in a query (because the lookup field is). If the queries are nested or complex, it can take some time to track down the lookup that's causing the error (that is, if it occurs to you).

So as a newbie I say hey maybe I should be careful with lookups. Whats your view, if you don't use lookups what do you do? Do you have any guides articles on other methodologies?

 

[namliam]

Dont use lookups, they are confusing misleading and distracting.

DONT use lookups in the tables, but DO use them on forms for the users to use when they are inputting data into your tables.

 

[flebber]

Went and got Access 2007 inside out, there above table datatypes in table 4-1, a warning not to use lookups in tables. Makes me doubt Matthew McDonald a bit, its a commonly recommended book and highly rated on amazon.

 

[namliam]

One man's genious is another man's mad man...

The point of lookups in tables is this, it will display the i.e. Department name in a column. But this is a lookup in another table.
Thus in this table it only stores the relational value /(foreign) key

As a result you will not be able to use 'Finanace' to filter on that column, as the column only contains 1,2,3,4,5,6 etc.
As long as you REMEMBER and REMEMBER and KNOW that, use it to your heart's content as far as I care, but .... and this is the thing, it then breaks again when you use it on forms, reports and queries where you need to make your own lookup to 'force' now what seems to automagicaly happen in your table.

So there is atleast like 3 or 4 points where things can get confused and since users -in general- shouldnt be touching your tables anyways, but rather should be working of forms... why use something that will only create potential confusion and add nothing real in functionality?

 

[Galaxiom]

I sometimes use lookups in tables for a little while but always remove them when I split the database.

Access automatically designs the lookups on controls in forms using the same information so it can be a timesaver where a field is used in several forms.

Lookups also makes it easier to become familiar with the actual data being represented. Once they are removed I then get familiar with the actual record. I think this is a best of both approach.

 

[Simon_MT]

Alternatively, you can put your Lookups into Modules and they can be used repeatedly and wherever they are needed.

Simon

 

[gemma-the-husky]

its not that you dont use the lookups, it's that you dont want access to pre-design them at the table level. you want to manage them manually at design stage- its a bit more work, but gives you control. In many cases, you dont want the basic lookup anyway, you want a different lookup that filters the full choice

as a user, you should never look at tables anyway
as a developer, you only really need to look when investigating problems - and in those cases you really want to see the true data, not the value of the stored lookup.