multiple table search problem (1 Viewer)

[reptar]

I have my main form which has a control source from a query containing fields from tbl_company. In addition, i have a subform in tabular view on the same main form which has a control source containing fields from tbl_contacts. Now, each individual company record has a tabular list of their multiple contacts. I would like to create a search feature which searchs either from company, contacts or both and then displays the filtered results in the main form format. I have played around with this by creating a master query containing fields from both company and contacts. My problem is that if the name "John" for instance is searched and appears twice in the contacts subform, the search will return the associated company record two times. Whereas i would like it to simply display one company record and one tabular subform showing John twice.

I hope this makes sense.

Any ideas most welcome.

Thanks.

 

[jwhite]

May I assume your structure is similar to:

tblCompanies
-------------------
CompanyID, AN PK
CompanName, Text
...

tblCompanyContacts
----------------------
CompanyContactID, AN PK
CompanyID, Number FK
ContactName, Text

If not like the above, there lies your main issue. If done like the above, then your Main Form would reference tblCompanies, your subform would reference tblCompanyContacts, the Master/Child Links would be set to CompanyID. Then, when you select a different CompanyID in the Main Form, the subform will automatically refresh with the Contacts of that Company.