I'm trying to link two tables on a common field -- [Business Segment] -- but I only want the unique values from the first value to show up in the appropriate column in the second table.

To clarify: the first table -- tblMaster -- has 3000 entries organized by [Employee ID]. [Business Segment] is a field of that table, but there are only 12 business segments in the company. In my second table -- tblGap -- I only want the 12 business segments to show up, as this table will be used to store information about each business segment, and the field [tblGap].[Business Segment] will be used to populate a combo box on a form bound to tblGap. It's also the primary key for tblGap.

Am I making any sense? I don't want to populate tblGap manually, becuase the segments in tblMaster may change from time to time, and I want the change to cascade down to tblGap from tblMaster. And without linking the tables, I have no idea how to populate that combo box.

Thanks for your help,
Andreas

andreas:

I am not sure that I am getting it right here... but, it sounds to me like you have a field in one table, from which you want to extract the unique entries to create a combo box in another table. If that is the case, you can do it with a query.

Write a query that pulls only from the field in question in your master table, and set the query property of Unique to 'yes.' Then, when you set the row source for the combo box, use the query as the source.

Hope this helps... Tom

Business segment is what most people would call a lookup code. It's list of values should be stored in a table. That table should be used to populate comboboxes on forms where the lookup code is associated with some record. Referential intedrity should be enforced but cascade update/delete should NOT be enabled. You don't want rows deleted from your main table if you delete a business segment but you do want to prevent the deletion of a business segment if there are any rows in the main db that reference it. When a new business segment is defined, it must be added first to this lookup table. Doing it the otherway around as you are planning, leaves way too much margin for error.