duplicate/redundant entries

[Matthew Worthe]

I'm running a query based on a table/form with several
subtables/subforms. Each name/primary key on the main
form has more than one entry on more than one form. When
I run my query, the query pulls up the same name many
times, one for each combination of entries that is
possible. So if "Matthew Worthe" has two letters going to
him on the "mail contacts" subtable, and two phone
conversations on "phone contacts", "Matthew Worthe"
appears on the query four times.

Is there a way to query a name based on criteria that
depends on subtables, and then have the query list all the
subtable entries without listing all combinations of the
subtable entries? I don't care to match "phone contacts"
with "mail contacts" - I just want to see which people
I've contacted at least once on both subforms.

Thanks.

 

[John Vinson]

Is there a way to query a name based on criteria that
depends on subtables, and then have the query list all the
subtable entries without listing all combinations of the
subtable entries? I don't care to match "phone contacts"
with "mail contacts" - I just want to see which people
I've contacted at least once on both subforms.

Actually there are several ways to do this.

1. Uncheck the Display checkbox on the related table fields that
you're using as criteria, and set the Query's Unique Values property
to Yes.

2. Use an IN clause:

SELECT <whatever>
FROM Contacts
WHERE ContactID IN(SELECT ContactID FROM [phone contacts] WHERE
<criteria>)
OR ContactiID IN(SELECT ContactID FROM [mail contacts] WHERE
<criteria>);

3. Possibly more efficient, try both: an EXISTS clause:

SELECT <whatever>
FROM Contacts
WHERE EXISTS
(SELECT ContactID FROM [Phone Contacts]
WHERE [Phone Contacts].[ContactID] = [Contacts].[ContactID]
AND <criteria>)
AND EXISTS
(SELECT ContactID FROM [Mail Contacts]
WHERE [Mail Contacts].[ContactID] = [Contacts].[ContactID]
AND <criteria>)