Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Look ups queries

Status
Not open for further replies.

Suzanne27

Technical User
Aug 14, 2002
1
GB
I'm putting together a fairly complex database with around 10 tables which need to be related to each other. I'm fairly new to this Access lark and am getting a bit confused about look ups and where it is best to add them. What should you consider when trying to decide whether to put a look up into the original table or entering in on a form instead? The end users of this database will probably never use the tables to enter data.
 
I don't understand quite what you mean. Are you talking about having a list or combo box, and deciding whether to make the values come from a table or to be bound to just the list/combo box instead?

If that is the case, I would always put them in the tables, unless it was a truly obvious field like male/female, east/west, etc.
 
If you define a Lookup on a field in a table, that lookup is in effect in EVERY place that the field is referenced. This eliminates having to code a lookup on multiple forms that use the same field -- When you create each form it will default to using the lookup that you defined on the table field.

For example, I have a field called StateCode on a table and define a combo-box lookup to look up the 2-letter abbreviation for each state (either in another table or as a set of static values). Once defined, if I add that field to a new form I will automatically get a combo-box for that field instead of a normal text field.

You would use a lookup on a form only it that form is the ONLY place that the lookup is needed or used. Otherwise, there is not much difference between the two.
 
Use tables - in almost all cases. Sooner or later someone is going to want to add something and it's just a lot easier to add it to the table then to revise a hard-coded set up. There's always a better way...
 
Suzanne27,
Some reasons NOT to use the look-up fields in tables:
1. When you are in the table/datasheet view, you may not see what is actually stored in the table, but some other value
2. The 'look-up' field will automatically create a relationship. It will create a new index. Depending on the size of the db, this may result in un-necessary bloat/work for Access.
3. Sorting may become difficult if you have nothing but the value, and not the data.
4. Tables are for data storage-not calculations, not look-ups.

IMHO, look-ups are best done by use of a query. Design it in the query once, that qry is now available ANY time you need to 'look-up'. The standard Leszynski, and Reddick Naming conventions even use the prefix, tlkp.... to be used to designate a table that stores data for use in look-up. The accepted standard for the query you design for look-ups is qlkp....... HTH, Montrose


Learn what you can and share what you know.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top