Hey, there, folks!
At my job, I’m the resident Access person – although in fact I know nothing about Access or about databases. So I’m attempting to teach myself as I go along, but the sad truth is I don’t know what I’m doing.
I have an SQL / Microsoft Access problem that I’m hoping someone out there knows the solution to. Please read on if you think you might be able to help….
Okay, so my employer – a local nonprofit – maintains a mailing list. The SQL for the query I use to generate the “regular” mailing list looks like this:
Mailing_list_query
SELECT Members.Zip, Members.[Last name], Members.[First name], Members.[Address 1], Members.[Address 2], Members.City, Members.State
FROM Members
WHERE (((Members.Zip)>”0″) AND ((Members.Deceased)<>Yes) AND ((Members.[No Mail])<>Yes))
ORDER BY Members.Zip, Members.[Last name];
Even an Access blockhead like me can work out what this means; from the table called “members,” I’m taking all the info I need for mailing labels, subtracted the members who have died or requested we not send mail, and outputting it in Zip code order (and in alphabetical order by last name within zip codes).
However, during our annual “pledge drive,” we have to divide the membership list in two – those who receive our regular mailer plus a “give us money please” letter, and those who receive only our regular mailer (because they’ve given us money too recently to ask them again).
So I use the following SQL to make Access give me a list of “everyone who has donated money to us since May first”:
GaveGiftsSince_May_1_2003
SELECT Members.Zip, Members.[First name], Members.[Last name], Members.[Address 1], Members.[Address 2], Members.City, Members.State
FROM Members INNER JOIN Gifts ON Members.MemberID = Gifts.MemberID
WHERE (((Members.Deceased)=0) AND ((Members.[No Mail])=0) AND ((Gifts.Date) Between #5/1/2003# And Date()))
ORDER BY Members.Zip, Members.[Last name];
This is pretty similar to the last one, actually. The big difference is that data from a second table – “Gifts” – is incorporated into this one. The outputs only those members whose ID# appears on both “Members” and “Gifts,” and who have given a gift since May first.
Now here’s my problem: I want to create a query that’ll give me a list of everyone in our database who hasn’t given us money since May 1, 2003.
I know what you’re thinking: you’re thinking, “that’s easy! All Amp has to do is change ((Gifts.Date) Between #5/1/2003# And Date())) to ((Gifts.Date) NOT Between #5/1/2003# And Date())).
Well, that was my first reflex, anyhow. The problem is, that gives me everyone who ever gave a gift before May 1, 2003; it doesn’t include the people on our mailing list who have never (yet) made a donation. And it does include people who have made a donation since May 1st, but also one (or more) before May 1st. Oh, and it gives me duplicate listings for everyone who gave multiple gifts before May 1st.
So – keeping in mind that this helps out a charitable organization – and, even more important, keeping in mind I’m an Access novice who needs even basic stuff spelled out for him – does anyone out there have a wonderful solution?.
When Im doing thing like this I like to approach in in as simple a way as possible – which I usually do by dividing the problem into two smaller problems
So, the first thing I would do is to make a new table – “recent gifts”
U do this by select into table recentgifts from gifts where gifts.date between…
Then I would compare the member table to the recentgifts table.
if the member id is in the recent gifts table, then one, otherwise…
Thats off the top of my head, but if u have trouble I can fire up my access and get u actual working code if u need it – Ill check back :)
Patrick O is on the right track, but I’d do it in a slightly more direct way:
SELECT Members.Zip, Members.[First name], Members.[Last name], Members.[Address 1], Members.[Address 2], Members.City, Members.State
FROM Members
WHERE Members.MemberID NOT IN
(SELECT Gifts.MemberID
FROM Gifts
GROUP BY Gifts.MemberID
HAVING MAX(Gifts.Date > #05/01/03#) )
That saves you from making the temporary table.
I believe this will work, though I haven’t tested it.
The idea is that the nested select is the IDs of the people who you don’t want to solicit–that’s the temporary table Patrick O was making–and that the outer select picks everyone else.
I’m not 100% sure of this, but I think the only problem is that you put the NOT in the wrong place. Try this:
AND (NOT ((Gifts.Date) Between #5/1/2003# And Date())))
If that doesn’t work for some reason, try this:
AND ((Gifts.Date) < #5/1/2003# OR (Gifts.Date) = NULL))
I would go to the query tab and use the “New Query” button to build a query (instead of plugging the SQL straight in), as that’s how Access is designed to be used – by clods like me not brainy SQL writers. Anyhoo, you make two queries – the Members not deceased and requiring a letter – and a query for Recent Gifts.
Use the “Make-table query” command to convert the queries to tables. Then go to the query tab again and use the “Find Unmatched” query wizard. (But, as i said, I’m a clod. Hope I’ve understood the question sufficiently.)
Oops! I typoed. Here’s a corrected version:
SELECT Members.Zip, Members.[First name], Members.[Last name], Members.[Address 1], Members.[Address 2], Members.City, Members.State
FROM Members
WHERE Members.MemberID NOT IN
(SELECT Gifts.MemberID
FROM Gifts
GROUP BY Gifts.MemberID
HAVING MAX(Gifts.Date) > #05/01/03# )
I agree with adamsj – I haven’t tested it either but it’s the right idea. (However, if you want to include May 1 in the range, substitute “>=” for “>”.)
This select-within-a-select method is also, I think, a better way to do the first task you described (find everyone who *did* give since May 1) because it will only give you one row per member, even if there were multiple gifts. You would use exactly the same query but change “NOT IN” to “IN”.
Yep – Kevin’s got the SQL right, I think but to get Access to do that itself from the query interface – on your original query just use the second criteria line underneath your current criteria line (the “or” line) that copies everything you’ve already got, but on the date field use “Is Null” instead of the “>#05/01/03# criteria.
That’s what those extra lines at the bottom are for ;). The important thing to remember is if one critera changes you need to still include the other non-changing criteria or it will interpret it as “(all non deceased and subscribing members who gave us $$ after May 2003) OR (all members who have never given us $$, be they alive or dead and subscribers or not).
If you ever need help feel free to email me a copy of you mdb file and I’ll help you out. This is what I do all day every day :)
You folks are great! Thank you! Mwwwaaah! Mwwwaaah!
I’ll next go to work on Wednesday, when I’ll try out some of these solutions. Thank you all!
There’s an excellent book on access:
Microsoft Access 2002 Comprehensive Concepts and Techniques
http://www.amazon.com/exec/obidos/ASIN/0789562820/qid=1072370911/sr=2-1/ref=sr_2_1/103-3107985-4661433
(I used their Access 97 version, and have browsed their Access 2000 version).
It’s very, very step-by-step, and covers a lot of material. Pair it up with ‘Learn SQL in 10 Minutes’, if you can get it. That’s a very quick reference on SQL.