Subform question

[Bent]

I have a contact database with company info in one table and person
info in another table. I use a form to display the company information
with a subform that show the emplyees of the company. The subform is
using the ID of the company table as the 'Link Master Field' and the
'Link Child Fields' is using a field in the person table that contains
the ID of the company.

I would now like to extend this such that one person can be associated
with more than one company and be displyed in the subform of either
company form. I added a field in the person information table for the
alternate company ID.

I have not been able to find a way to show the same person in two
company subforms, though. The 'Link master Field' would be the same,
but adding another 'Link Child Fields' is ANDing the two, i.e. it then
only displays the person when the same ID is in both child link fields.
I could not find a way to OR them.

Can anyone help?

thanks in advance
Bent

 

[Joan Wild]

I have a contact database with company info in one table and person
info in another table. I use a form to display the company information
with a subform that show the emplyees of the company. The subform is
using the ID of the company table as the 'Link Master Field' and the
'Link Child Fields' is using a field in the person table that contains
the ID of the company.

I would now like to extend this such that one person can be associated
with more than one company and be displyed in the subform of either
company form. I added a field in the person information table for the
alternate company ID.

No that isn't the way to go. You now need a third table - a junction table
to resolve the many to many relationship. One person can work for one or
more companies, and one company has one or more people working for it.

This junction table would contain the company ID and the Person ID. You can
start by copying the Person table and giving it a name of tblCompanyPerson.
Then just delete the excess fields from it. Now you can add additional
records for the PersonID and second companyID they work for.

For you form, you'd change the subform record source to a query based on the
junction table and person table. The link child field would be the Company
ID in the junction table.

 

[Bent]

Joan,

Thanks much! I had a hunch I had to go this way, but had trouble making
it work. I found an example, so I should figure it out now.

Bent