Look ups queries

[Suzanne27]

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.

 

[RiverGuy]

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.

 

[wemeier]

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.

 

[tviman]

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

 

[montrose]

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.