Querying Values Within a "Group"

[Milin]

I have an Access97 Database (I realize that Access has it's own SQL &quot;flavor&quotwhere I want to track respondents to a direct mail campaign. I track the name & address of who is sent the campaign and code their name entry with a unique campaign code (&quot;MediaCode&quot; field). When they respond - incomming with a different campaign code (&quot;MediaCode&quot; field), they are entered into the database again....so I will have two records of the same person, but with different MediaCodes. I then have a field where I group those of same address together --I'm householding them (the &quot;DupeGroup&quot; field). What I am trying to do is write an SQL statement that says &quot;find the records that have the same DupeGroup number. Within each of those DupeGroups, show me only the DupeGroups that have records containing MediaCode X and MediaCode Y in a MediaCode field.<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.MediaCode<br>
FROM Contacts<br>
WHERE (((Contacts.GroupDupe)=[GroupDupe]) AND ((Contacts.MediaCode)=&quot;DM1&quot; And (Contacts.MediaCode)=&quot;DM1R&quot);<br>
<br>
Thanks for helping a newbie!<br>

 

[calahans]

Hi Milin,<br>
<br>
I'm finding it hard to follow you (mondays!!) but I think I can see the problem. Its to do with the following SQL:<br>
<br>
((Contacts.MediaCode)=&quot;DM1&quot; And (Contacts.MediaCode)=&quot;DM1R&quot)<br>
<br>
You are checking the one field for two different values, if effect you are saying give me rows where x=1 and x=2. This is a logical impossiblity. Have you tried changing the AND for an OR<br>
<br>
<br>
SELECT Contacts.GroupDupe, Contacts.FirstName, Contacts.LastName, Contacts.MediaCode<br>
FROM Contacts<br>
WHERE Contacts.GroupDupe=GroupDupe AND (Contacts.MediaCode=&quot;DM1&quot; <b>OR</b> Contacts.MediaCode=&quot;DM1R&quot;<br>
<br>
I have got rid of those horrible Access brackets!!<br>
<br>
HTH<br>
<br>
C<br>

 

[bigscouse]

Hello Milin,<br>
I think you want to list GroupDupes with more than one entry<br>
and containing both MediaCodes. If so then try this (not sure if it works in access): -<br>
<br>
SELECT Contacts.GroupDupe, Contacts.FirstName, Contacts.LastName, Contacts.MediaCode<br>
FROM Contacts Contacts<br>
WHERE Contacts.MediaCode=&quot;DM1&quot; <br>
AND EXISTS (<br>
SELECT *<br>
FROM Contacts Con2<br>
WHERE Con2.MediaCode=&quot;DM1R&quot;<br>
AND Contacts.GroupDupe=Con2.GroupDupe); <br>
<br>
<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>