Need help with database design

[Tacop84]

I am creating a Contacts database for a customer using the built-in Contacts template within Office 2007. I can do just about everything with the template except for one request.

Included in the template is a Company field. I created another field called Relationships. The purpose of this field is to populate all of the companies related to the company listed in the company field for each unique record.

For example if I have Company ABC and they do business with Company X, Company Y and Company Z I would like the relationship field to be automatically populated with Companies X Y and Z as a drop down menu. If Company 123 does business with only company X then I want the relationship field to only be populated in a drop down menu with Company X.

In programming terms I want it to function like an if then statement. If Company field = Company ABC then populate the Relationship field with Company X Y and Z.



Is this possible? Would I need a unique table for each company?

[hertfordkc]

You don't need another field called Relationships: you need a Table called Relationships which is linked to the first table. When you recall Company XYZ, the relationships can be display in a subform or subreport.