Writing an SQL subquery statement

[CLH]

I need a subquery to go within a query that will show
subscribers with newsletter_code "AAA" and "GGG" but
not "LLL" newsletter code. How would I do this? I am
very raw on this topic-sorry.

 

[Allen Browne]

Presumably have have a table with these fields:
- SubscriberID foreign key to tblSubscriber.SubscriberID
- Newsletter_Code The code for the newsletter.
The table contains a record for each combination of subscriber and
newsletter.

Then you need 2 subqueries to get the result.

Create a query into this table (joined with your Subscriber table if
desired).
Drag newsletter_code into the output grid.
In the Criteria row under this field:
"AAA"
This yields the customers who get this newsletter.

The first subquery will be:
EXISTS (SELECT newsletter_code FROM tblNewsletter AS Dupe
WHERE (Dupe.SubscriberID = tblSubscriber.SubscriberID)
AND (Dupe.Newsletter = 'GGG') )

The 2nd subquery will be:
NOT EXISTS (SELECT newsletter_code FROM tblNewsletter AS Dupe2
WHERE (Dupe2.SubscriberID = tblSubscriber.SubscriberID)
AND (Dupe2.Newsletter = 'LLL') )

 

[Guest]

Allen

Thank you-I will try this first thing this morning. I appreciate the help

Sincerely
CLH