Hey, there, folks!<\/p>\n
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.<\/p>\n
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….
\n
\nOkay, 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:<\/p>\n
Mailing_list_query<\/b><\/p>\n
SELECT Members.Zip, Members.[Last name], Members.[First name], Members.[Address 1], Members.[Address 2], Members.City, Members.State<\/p>\n
FROM Members<\/p>\n
WHERE (((Members.Zip)>”0″) AND ((Members.Deceased)<>Yes) AND ((Members.[No Mail])<>Yes))<\/p>\n
ORDER BY Members.Zip, Members.[Last name];<\/p><\/blockquote>\n
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).<\/p>\n
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).<\/p>\n
So I use the following SQL to make Access give me a list of “everyone who has donated money to us since May first”:<\/p>\n
GaveGiftsSince_May_1_2003<\/b><\/p>\n
SELECT Members.Zip, Members.[First name], Members.[Last name], Members.[Address 1], Members.[Address 2], Members.City, Members.State<\/p>\n
FROM Members INNER JOIN Gifts ON Members.MemberID = Gifts.MemberID<\/p>\n
WHERE (((Members.Deceased)=0) AND ((Members.[No Mail])=0) AND ((Gifts.Date) Between #5\/1\/2003# And Date()))<\/p>\n
ORDER BY Members.Zip, Members.[Last name];<\/p><\/blockquote>\n
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.<\/p>\n
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<\/i> given us money since May 1, 2003.<\/p>\n
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<\/b> Between #5\/1\/2003# And Date())).<\/p>\n
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<\/i> include people who have made a donation since May 1st, but also<\/i> one (or more) before May 1st. Oh, and it gives me duplicate listings for everyone who gave multiple gifts before May 1st.<\/p>\n
So – keeping in mind that this helps out a charitable organization<\/a> – 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?.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading