Question about joinging two queries on a form.

[kjspear]

I have a question about joining two queries on one form. I see that I can use the UNION statement to join two queries together. This works fine. However, I have a form that uses one of the queries. This form allows the user to check off one or more selections for example the user can check off the Decison Maker and/or Influencer of a company. This works and that's how it's been. The problem I'm having now is that I added another table for another group of members. The first query is for all of the primary people, now the second table is for the secondary or heirarchy of the first. I know the Union statement works as I already used it. However, is there a way that I can use the same form to query both tables. In other words, I have a check box bound to TABLE1, can that same check box be bound to TABLE2, so that it will work on both. Please let me know if you need further detail.

Thanks
KJ

 

[willir]

KJ

Another approach would to have one table, and define the type of contact. In fact, a person can be more than one type of contact -- decision maker, purchasing and research -- would suggest that you can treat the type of contact as a many-to-many relationship.

For example...

tblContact
ContactID - primary name
ContactLN - last name
ContactFN - first name
ComapnyCode - foreign key to company

tblComapny
CompanyCode - primary key
CompnayName

ContactTypeCode
ContactTypeCode - primary key
TypeDescription - text

ContactType
ContactID - foreign key to tblContact
ContactTypeCode - foreign key to tblContactTypeCode

Sample data...
[tt][COLOR=blueContactType[/color
ContactID ContactTypeCode [/b]
1 Owner
1 Decision maker
2 Consultant
2 Influencer
3 Technical
3 Influencer
[/tt]

This way, you can view...
- contact types for a person
- company contacts and their contact type
- all contacts for specific contact type(s)

If you work with consultants, then you will have consultants who may be "influencers" more than one company. In which case, you would have to treat the Contacts and Company tables also as a many-to-many.

Why do I prefer this solution over union queries? From my perspective, much simpler.

Presentation
Getting back to displaying the data. You can use either an unbound combo box or list box to apply a filter / record source on the form. Since you seem to want to see more than type, perhaps using a multi-select listbox may suite your needs better.

Richard