Lookup Fields (1 Viewer)

[crazymarvin]

I have two tables(tblInvoiceItem and tblItems). On both of these tables there is; ItemNo and ItemCost. tblItems already has various records about items. I want to be able to have a lookup field for ItemNo on tblInvoiceItems which will populate the ItemCost from tblItems.

Basically when I pick an item number(lookup field on tblInoviceItem, looking up from tblItems), I want the price which corrisponds with it to appear in the correct field(ItemCost on tblInoviceItems).

Hopefully I explained that okay

 

[RuralGuy]

The Evils of Lookup Fields in Tables

 

[KKilfoil]

I respectfully disagree completely and emphatically with ruralguy's link.

The pro's of Lookups are well documented here and on other Access (or other relational dbs) sites, whereas each of the con's linked above are easy to avoid with good db design.

 

[KKilfoil]

Back to the original issue:

Do these prices change with time, or are they absolutely constant? If they are constant, you do not want to store them in another table. Build a relationship and look them up as needed.

So, the answer depends on the stability of the price values.


If they DO change over time, then you DO need to store the prices at the time of interest.

 

[RoyVidar]

Could you list any of the pros, KKilfoil?

My understanding is that table level lookups are not part of good db design for all the reasons listed at TheAccessWeb.

I would recommend usage of combos on forms in stead, and in this case, perhaps with some smallish after update code?

 

[boblarson]

With proper database design (that would include not allowing your users to access the tables directly), that would preclude the use of lookups at the table level because the user would never be touching the tables directly.

There is really no benefit to using lookups at the table level and it only causes issues for those who are not highly technical users. In my 10 years of Access development and programming, I have never found the need to use them and therefore I would recommed to not use lookups at the table level.