Lookup - to use but not to use in query, ok?

[Harrold]

Hi

I read http://access.mvps.org/access/lookupfields.htm and not fully understand it. But i would like to clarify

1) Lookup is fine as long as i dont use it in queries/form/report.
2) if i dont use lookup in table, how am i able to record the entry in related table? with just ID is difficult when i dont know what it refers to and i need to keep refer to the parent table to check.

Thanks

 

[Dhamdard]

There are advantages and disadvantages of using lookup. I found lookup fields very effective and useful in data consistency. It can help in tables, forms, queries, reports and all sort of analysis and can be used anywhere when required.

Could you provide us with more specific concern or question you have?

 

[spikepl]

Lookup fields in tables are an Access feature and should simply not be used, as outlined in the article you linked to. They create a buried relationship between tables, that you should create explicitly and visibly whenever needed in a query.

Simply forget about the existence of lookup fields in tables.

Use the Relations window to create relations between tables, or do it in the query design, where you can join tables as needed.

 

[stopher]

I agree with spikepl. Don't even consider implementing lookups in your tables.

But just to be clear, the link is talking specifically about lookups in tables. There's no reason at all why you shouldn't implement the effect of lookups (e.g. a combo) in your forms. This is good practice. So you can easily have a combo that shows the name to the user but actually stores the index in the table.

Note that you should not be editing or entering data directly into tables or queries as a general rule so there's no need for lookups there.

Chris

 

[Harrold]

Thanks all.

I am reading Access 2007 for Dummies. and the sequence is 1) Essential Concepts, 2) Tables, 3) Queries, 4) Forms, 5) Reports, 6) Macros.

Lookup wizard is explained in Tables section. and it also show how to edit and enter data in table. so i am quite confused as forum here keep emphasising not to enter data in tables.

So far, i only learn up to tables, query/form/reports havent been gone through in details yet.

Why Dummies put look up wizard under tables and not other sections?

 

[rodmc]

What Ive done is put some data into my tables and the used a query for lookup instead of creating a table as such

 

[spikepl]

Tables are containers for holding data. During development of course you need to put data into the container, when you have no user interface to do it with. But - that data is entirely unprotected. You cannot check user input, verify it, reject it, or anything else - that's what forms are for - to interact with the data. Queries are sort of views of data in the containers, in many respects entirely similar to tables, but also leave the data unprotected.

 

[boblarson]

Why Dummies put look up wizard under tables and not other sections?

Because that feature exists and the author is covering that feature, but that author may also not be one to follow "best practices" and you would be hard pressed to find Access MVP's or other professional Access developers who would recommend that people new to Access use that feature. It can be a useful feature IF you understand the pitfalls and the limitations but a person new to Access can't make those assessments because they don't have the knowledge yet to be able to judge when to use them and when to not.

So, the majority of us (and I will admit there are those who do not agree with what I am about to say) would state - DO NOT USE LOOKUPS AT TABLE LEVEL! Use them on FORMS.

And lookups are not necessary on anything but forms. You don't need them on Reports, nor in queries as you would just join the lookup tables into the report's record source query and in a query it would just be included in that query.

 

[gemma-the-husky]

hers an example

lets say you have an address table, one field of which is the state (US)

so you actual store the state abbreviation in your table (TX, AZ, etc)

you have another table (the lookup table) which holds the linked abbreviation, and the full name.

Now when you define the address table, you can set the state field to be a lookup - so that every time you use the state abbreviation, access automatically replaces the abbreviation with a combo box, using the lookup table.

Now this all works Ok, but if you ever want to "see" the actual data held in the table, it isn't so easy to get at the "real" data. You may also find the data isn't sorted in the correct order - eg do you want it sorted in state order, alphabetical order, or some other order. In somne cases, you may want to exclude certain states. So you find yourself modfiiyng the standard query that access uses for the lookup, anyway.

So the upshot of all this, is that you may as well not bother. It's "cleaner" to roll it yourself, when you need it, and hardly any more difficult.

And this goes for quite a few non-standard access "facilities" such as multi-valued fields.

 

[boblarson]

The key "takeaway" from all of this is -

Microsoft has put features into Access. Not all of these features would be considered wise to use. Just because it is there, doesn't mean it is good to use.