duplicates in listbox or a wrong querry

[pawelch]

Dear all,

Suppose that there are 4 tables, Person, Company, PersonRoleInCompany, CompanyContacts.

In PersonRoleInCompany, there are PersonId, CompanyId, RoleID,
In CompanyContacts, PersonId, CompanyId, ContactTypeId,

a person can be assigned to multiple companies and have multiple roles within a company. That implies that in PersonRoleInCompany there are multiple entries of the same person, similarly there are multiple entries of the same person in company contacts (since it is assigned to many companies - thus it must be either of a main contact type or another)

The problem is that I would like to select distinctively person details given its type AND contact type. I have tried multiple queries but I always end up with duplicates or triplicates in my list box.


I have tried to remove duplicates with a hideduplicates method but it did not work. I thought that maybe I select all the entries and afterwards remove duplicated. However it did not work. It's because my list is of a Tab/Query type and I cannot change its property Row Property Type to Value list.

My dear all, how would you approach this problem?
Thank you in advance for any suggestions.

Cheers!

 

[boblarson]

You would need something like this to concatenate the roles into one column for display while selecting distinct from CompanyContacts.

 

[pawelch]

Hi Bob Larson,

I could not open the link, however I think I have done what you suggested. In other words, I have to have created a function that generated SQL query given the condition.
It took me a while but it works a treat.

Thank you for your response anyway.

Cheers