I’m having a database problem at work that I don’t know enough to fix. If any “Alas” readers who know database stuff can glance at this and tell me if they see what my problem is — my problem involving the database, that is, I may have many non-database-related issues that there’s no need to bring up at this moment — I’d appreciate it.
Heck — I’ll do a cartoon of any celebrity or any “Hereville” character of your choice to anyone who fixes the problem!
So at my workplace, a nonprofit historic site, we use Microsoft Access to keep track of donations and addresses.
Today, we want to output a list of everyone who hasn’t given us money since August 1 2007. To do this, we use a query that looks like this (for posting on “Alas,” I’ve replaced ( with { and ) with }, because posting double-parenthesis in “Alas” leads to difficulties. But in the real thing I’m using parenthesis):
SELECT Members.Zip, Members.[First name], Members.[Last name], Members.[Address 1], Members.[Address 2], Members.City, Members.State
FROM Members
WHERE {{{Members.Zip}>”0″} AND {{Members.MemberID} Not In {SELECT Gifts.MemberID FROM Gifts GROUP BY Gifts.MemberID HAVING MAX{Gifts.Date} >= #01/14/08# }} AND {{Members.Deceased}<>Yes} AND {{Members.[No Mail]}<>Yes} AND {{Members.Retirement}<>Yes} AND {{Members.[No Begging]}<>Yes}}
ORDER BY Members.Zip, Members.[Last name];
I have no idea how to use this stuff, but this is a query that I’ve used many times in the past, and all I have to do is change the date and it works. Until today.
Today, for some reason, it won’t work for dates before 01/14/08. If I input 01/14/08, it appears to correctly output a list of every member who hasn’t donated money since the 14th {which is to say, pretty much all of them}. But any date earlier than that — such as 01/13/08 or the date I want to use, 08/01/07 — and it outputs a blank list, indicating that absolutely all of our members have given money since that date. {We’d be happy if that were so, but alas, it’s not so.}
Oddly enough, another query I use — this time to tell me everyone who HAS given us money since August 1 2007, plus the retirement homes on our mailing list — seems to be working perfectly. Here’s what that one looks like:
SELECT Members.Zip, Members.[First name], Members.[Last name], Members.[Address 1], Members.[Address 2], Members.City, Members.State
FROM Members
WHERE {{{Members.Zip}>”0″} AND {{Members.MemberID} In {SELECT Gifts.MemberID FROM Gifts GROUP BY Gifts.MemberID HAVING MAX{Gifts.Date} >= #08/01/07# }} AND {{Members.Deceased}<>Yes} AND {{Members.[No Mail]}<>Yes}} OR {{{Members.Retirement}=Yes}}
ORDER BY Members.Zip, Members.[Last name];
To qualify as having “solved” my problem, you have to explain it to me in terms so clear that I’m capable of getting it working. :-}
Has someone changed the date format preference in Access?
I can’t imagine they have — unless that’s something it’s possible to do by accident. Also, it seems to me that both the working and the non-working query have a date in the same format.
Is there a way I could test for that, that you know of?
(Also, this is something in the database, not in a particular computer’s version of Access — I’ve tried the database on three different computers today. Of course, maybe the date format preference is saved with the database?)
Never mind, that couldn’t be it because like you said, the second query. Hmm.
Your queries are fine, at least to my eyes. But you knew that, because they used to work. Something has kludged your data.
Try running this:
SELECT Gifts.MemberID FROM Gifts GROUP BY Gifts.MemberID HAVING MAX{Gifts.Date} >= #01/14/08#
as a standalone and see what your output is for various dates.
That’s your problem right there.
Bwhahahahhahahahaha!
Sorry. I was feeling immature and had to get that out of my system somehow.
Sorry, Bob, I don’t know how to run something as a standalone. I tried sticking it into the current query, so it now reads this:
But that just returns a “at most one record can be returned by this subquery” error.
The query seems fine to me, but of course, you have had to edit it to post it, thus there may be a typo somewhere in the original. Still, I doubt it. It is a lot more likely that there is something unexpected in the data to which you are applying it.
If this were my problem, I would do exactly what Robert suggested, and would try to isolate the problem.
The first thing to do is to run this: (use whatever parenthesis your system requires)
SELECT Gifts.MemberID, MAX(Gifts.Date)
FROM Gifts
GROUP BY Gifts.MemberID
HAVING MAX(Gifts.Date) >= #01/01/07#
This should give you a list of everyone who has donated anything since the beginning of last year, and the dates of their last gift. Is there anything strange about the results? Does a date appear too often? Does it look as if someone purged records? Tell us what you get, and I’ll give you the next step.
An interesting result would be if you get all dates higher than 01/13/08. Then I would go look for a incomplete/malformed record with that date in the Gifts table, or for the reason of made-up gifts showing at that time.
Whatever. Run it and let us know.
Not sure if it is right but you have 2 different kinds of quotes here:
WHERE {{{Members.Zip}>”0″
You also have 15 left parens and 14 right parens (parenthesis).
I mean just run that text as the whole query. It’s a legit query in its own right, it should run just fine. I did leave off the semicolon, so it would need to be
SELECT Gifts.MemberID FROM Gifts GROUP BY Gifts.MemberID HAVING MAX{Gifts.Date} >= #01/14/08#;
Ampersand,
To run as a standalone, just go into Access, query/new/design view; when the query pops up, change the view to SQL; and paste in the code Petar posted. (Robert beat me to it.)
Also, could you try putting in the date code. Type the date into Excel, then change the cell format to “number”; 8/1/2007 is 39295.
I, too, suspect some problem in the underlying data tables.
PROBLEM SOLVED!
And yes, it was a problem in one of the underlying data tables.
First of all, thank you Robert for providing the essential info that I had to add a semicolon at the end; that was the thing I couldn’t figure out about running a standalone. (I had figured out the info SamC gave myself).
I had already known that the data for gift dates wasn’t screwed up (that was one of the first things I checked, by opening the data table directly and scanning the date column). However, when I ran Petar’s query (thank you, Petar) and scanned the results, I noticed that there was one gift which didn’t have an associated member ID — which is wrong. All gifts should have member IDs, indicating who gave the gift. (In fact, it shouldn’t even be possible to enter a gift without associating it with a member ID).
So I went to the data table, deleted the gift with no associated member ID, and now everything’s working perfectly. :-)
Thank you all for your help.
Okay, so does this count as having solved the problem myself; or do I owe Petar and Robert cartoons? I think maybe I owe Petar and Robert sketches. So would either or both of you like me to do a character or celebrity?
Sorry about the semicolon. I use mysql which is forgiving about things like that, and as is usually the case, crimes that are easily forgiven are readily committed.
You don’t owe me anything, but I am trying to gather ideas and inspirations for a new logo for my company, DocRocket – if you want to sketch a cute and/or cool rocket ship, that would be great, because my graphic artist has a serious rocket ship drawing deficit. Something 50s-ish?
So, uh, why would having an empty Gifts.MemberID cause the query to return nothing? I assume that the date of the donorless gift was 1/14/08…
It might be worth your time, Amp, to have Robert or someone else you trust beat relational into you. It only took me a few days at work to get the basics, and then you’ll be able to troubleshoot this sort of thing more easily. Compared to programming, writing SELECT statements is very easy and can be learned quickly.
I wish I had a book to recommend on the subject, but I learned by doing.
Well, drawing machinery really isn’t my strong suit, but I gave it a shot…
Close — it was 1/13/08. What was happening is that no set that included the donorless gift could be generated correctly. I have no idea why.
Probably it would be worth my time to learn it, Plunky, but right now all my spare time is going into cartooning.
That’s pretty cool. Thanks!
I’m glad I could help, and it was a small thing compared to all I’ve learned from the discussions here. I do not feel you owe me anything.
I wish I had a book to recommend on the subject, but I learned by doing.
I found SQL Fundamentals to be an extraordinarily useful guide to learning both the Oracle and Access versions.