Help writing a query

In Access

Select field1, Field2 from Table1 Where DateUnsubscribed  Is Not Null

Select allOpen in new window

revised...

If Bob subscribed in 2013, then cancelled, then subscribed again in Nov of 2014, he is in the database twice.  Using your query, I would return his current active subscription, but I would not know that he had previously subscribed.  I need to find everyone who has cancelled, and not re-subscribed at a later date.

Select email, unsubscribedate from subscriptions where  unsubscribedate is null AND email not in (select email from subscriptions where unsubscribedate is null) 

Select allOpen in new window

Correction:

Robert's solution is good but I thing needs to move not from the outer query to the inner query:
Select email, unsubscribedate from subscriptions where  unsubscribedate is null AND email in (select email from subscriptions where unsubscribedate is not null)

You have not told us much about the table(s)

You say that "there are two records for that subscriber"
HOW do we recognize this? are you using the same subscriber number in both records?

I suggest you provide "sample data" and the "expected result" from that data

Robert,
Your query returned 0 records.

Email            DateUnsubscribed
--------             ------------
r@r.com       10/27/14
r@r.com      
b@b.com      10/14/13
c@c.com        12/12/14

Everyone with a date after the email is a subscriber who cancelled.
If NULL in the DateUnsubscriber field, then the subscription is active
r@r.com previously subscriber, then cancelled his subscription, but re-subscribed at a later date.
I want a list of subscribers who do not have an active account.
The desired list would contain b@b.com and c@c.com

did you try:

Select email, unsubscribedate from subscriptions where  unsubscribedate is null AND email in (select email from subscriptions where unsubscribedate is not null)

eghtebas,

Yes, I tried: Select email, unsubscribedate from subscriptions where  unsubscribedate is null AND email in (select email from subscriptions where unsubscribedate is not null)

I got a strange list of 10 subscribers.  The number should be about 200.

sorry, thought the name of table is members

select a.email, a.DateUnsubscribed
from subscriptions as a inner join (select b.email, Count(b.email) as CountOfemail
from subscriptions as b
group by b.email
having Count(b.email)=1)  as c ON a.email = c.email
where a.DateUnsubscribed is not null

Bingo! we have a winner.
Thank you Rey!!!

The table name was members.

rrhandle8,
You may wish to add another Many To Many for the subscription status and date.
For example, what happens if a person unsubscribes/subscribes multiple times?
tblSubscriptionStatus
ssID
ss_mID
ssStatus (Unsubscribe/Subscribe="U" or "S")
ssStatusDate
ssReason

Create a query that joins the member stable and this new table, and gets the max date for each member(qryMaxStatusDates):
SELECT tblSubscriptStatus.ss_mID, Max(tblSubscriptStatus.ssStatusDate) AS MaxOfssStatusDate, tblMembers.mEmail, tblMembers.mName
FROM tblMembers INNER JOIN tblSubscriptStatus ON tblMembers.mID = tblSubscriptStatus.ss_mID
GROUP BY tblSubscriptStatus.ss_mID, tblMembers.mEmail, tblMembers.mName;

Then create a second query (based on this query) to get only the Unsuscribed members (qryEmailUnsubscribed):
SELECT qryMaxStatusDates.ss_mID, qryMaxStatusDates.mEmail, qryMaxStatusDates.MaxOfssStatusDate, DLookUp("ssStatus","tblSubScriptStatus","ss_mID=" & [ss_mID] & " And " & "ssStatusDate=" & "#" & [MaxOfssStatusDate] & "#") AS Status, qryMaxStatusDates.mName
FROM qryMaxStatusDates
WHERE (((DLookUp("ssStatus","tblSubScriptStatus","ss_mID=" & [ss_mID] & " And " & "ssStatusDate=" & "#" & [MaxOfssStatusDate] & "#"))="u"));

Then loop this query to send your email:
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("qryEmailUnsubscribed")
rst.MoveFirst
Do Until rst.EOF
    DoCmd.SendObject acSendNoObject, , , rst!mEmail, , , "Special Offer for " & rst!mName, "Here is your special offer.", True
    rst.MoveNext
Loop
MsgBox "Done"

rst.Close
Set rst = Nothing

this woks for me, ...sample database is attached.
You can modify the code to send emails automatically, ...etc,
...but first tell me if something like this would work for you...

JeffCoachman
Database80.mdb