CODEWITHSUNDEEP
ms-accesslookup-tables
Chelle
Chelle

Reputation: 177

Access 2007 referential integrity without using lookup within table

I've seen various posts here about not using lookup within a table. If you don't, how do you enforce referential integrity between a field and the allowable values from a lookup table? I can't create a relationship between the table field and the field in the lookup table because I can't create a unique (no duplicates) index on the field - the particular value needs to appear multiple times across the records in the table. But if I use the field properties to set a lookup on it and specify the field from the lookup table that it must contain, then this ensures that data can't be entered into this field that isn't in the lookup table.

Or have I got completely the wrong end of the stick here?

Upvotes: 1

Views: 1057

Answers (1)

HK1
HK1

Reputation: 12210

I recommend that you always create forms for editing records. And in those forms you can create combo boxes that perform the lookups in the correct table and field. There are options there so that you can limit the data entry to only those values which are stored in the table. This option is called Limit To List (you'll see it in the combo properties).

Another important way to enforce that data exists in your lookup table is through your Relationships.

tblProducts
  ProductID (primary key)
  CategoryID (foreign key)
  ProductDescription

tblCategories
  CategoryID (primary key)
  Category

In the relationships window you would define a relationship between the two tables above on the CategoryID field. You are accomplishing several things here. It's more efficient to store only the CategoryID in your Products Table since you will be storing less data. Also, this way if you change the name on the category all records will reflect that change immediately. Every place that you display a product with its category you will need to create a combo box so that you display the Category Description as opposed to displaying the CategoryID.

As a side note, I recommend that you rarely use the Value List option as the Row Source Type in a combo. Using the Table/Query option and then creating an appropriate lookup table is a much more robust and flexible design.

Upvotes: 1

Related Questions