SQL Search : Querying Values Within a "Group" - Market Research

[Milin]

In an Access97 database, I want to track respondents to direct mail campaigns. I track the name & address of those who respond to the campaigns, and code their name entry with a unique campaign code (MediaCode field) Each &quot;line item&quot; of name, address, mediacode is entered into my Contacts table. I then have a field where I &quot;group&quot; or tag those of matching address together --I'm &quot;householding&quot; them with a unique assigned number(the DupeGroup field). This DupeGroup number is also on the same line-item line as each entry. What I am trying to do is write an SQL statement that says &quot;find the line-item records in table:Contacts that have the same DupeGroup number (&quot;see&quot; my households). Within each of those matching same DupeGroup line-items, show me only the records from those qualifying DupeGroups that have an entry in a MediaCode field of X and and entry in another line-item within that same DupeGroup number of a MediaCode of Y. In business terms, I want to identify which of the households that have responded to two different mailing campaigns and list the names of those people / DupeGroup numbers<br>
<br>
This is as far as I've gone, but it doesn't work:<br>
<br>
SELECT Contacts.GroupDupe, Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.MediaCode<br>
FROM Contacts<br>
WHERE (((Contacts.GroupDupe)=[GroupDupe]) AND ((Contacts.MediaCode)=&quot;AJ1299&quot; And (Contacts.MediaCode)=&quot;GD0100&quot);<br>
<br>
I hope my explanation makes sense - thanks

 

[JimHorton]

Try:<br>
<br>
SELECT Contacts.GroupDupe, Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.MediaCode<br>
FROM Contacts<br>
WHERE GroupDupe IN (Select GroupDupe from Contacts WHERE Contacts.MediaCode=&quot;AJ1299&quot<br>
AND<br>
GroupDupe IN (Select GroupDupe from Contacts WHERE Contacts.MediaCode=&quot;GD0100&quot<br>
--Jim

 

[Milin]

THANK YOU SO MUCH JIM!<br>
<br>
Can I ask you , or any others out there another newbie SQL question. How do I write the same type of question as above, but say &quot; find a GroupDupe with line-items where there are greater than two unique MediaCodes&quot;<br>
<br>
Thanks