Help writing a query

Table "Members" contains a list of subscribers over the last 10 years.  Some subscribers cancelled their subscription, but subscribed again at a later date. In this case, there are two records for that subscriber.  One record contains the date of the cancellation in the field "DateUnsubscribed", the other record as a null in the field "DateUnsubscribed".

Each record contains multiple fields, but the ones I believe are of importance are:
Email and DateUnsubscribed (which is null in the case of an active subscriber).
 
I want to send a special offer to those subscribers who are not currently subscribed.

The database is MS ACCESS.
rrhandle8Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
In Access
Select field1, Field2 from Table1 Where DateUnsubscribed  Is Not Null

Open in new window


revised...
0
rrhandle8Author Commented:
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.
0
Robert ShermanOwnerCommented:
Select email, unsubscribedate from subscriptions where  unsubscribedate is null AND email not in (select email from subscriptions where unsubscribedate is null) 

Open in new window


Correction:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperCommented:
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)
0
PortletPaulfreelancerCommented:
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
0
rrhandle8Author Commented:
Robert,
Your query returned 0 records.
0
rrhandle8Author Commented:
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
0
Mike EghtebasDatabase and Application DeveloperCommented:
did you try:

Select email, unsubscribedate from subscriptions where  unsubscribedate is null AND email in (select email from subscriptions where unsubscribedate is not null)
0
rrhandle8Author Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
try this

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
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
0
rrhandle8Author Commented:
Bingo! we have a winner.
Thank you Rey!!!
0
rrhandle8Author Commented:
The table name was members.
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.