Need query to find people who have not donated in the past year

[db2]

I have several tables containing information about donors to our charity. We need to send a letter to donors that have not given in the past year, who have also given an amount equal to or larger than $100 in the past.

My main concern is the first part. We have a "contributor data" table that contains the information (address, phone, etc), and a "donations" table that holds the information about specific donations made. The donor ID number is the link between these tables. In the donations table there is a field for donation amount and donation date.

If I add date criteria that says "<#1/4/2007#" obviously it will list all donations before this date. I'm trying to figure out how to find people who do not have a donation since this date.

Any suggestions are much appreciated!

 

[db2]

To clarify, I can't figure out how to say "only show id's that do not have donations after 01/04/07".

 

[Kiwiman]

Howzit

It will be something like

SELECT tblContributors.DonorId
FROM tblContributors LEFT JOIN tblDonations ON tblContributors.DonorId = tblDonations .DonorId
WHERE (((tblDonations .DonorId) Is Null) AND ((tblDonations .DonDate)<#01/04/2007#));

 

[MSAccessRookie]

This example will get you both sides of your request (previously over $100.00 and not since 01/04/2007):

SELECT tblContributors.DonorId
FROM tblContributors LEFT JOIN tblDonations ON tblContributors.DonorId = tblDonations .DonorId
WHERE ( ( Max((tblDonations .DonationAmount)) >= 100 ) AND
( Max((tblDonations .DonDate))<#01/04/2007# ) );

 

[db2]

Thanks for your help,

I'm still pretty foggy on SQL, but with your example and another I came up with this following code. Does this make sense to you? Also, if this is correct where could I add a clause to check if the donor has previously given $100 or more at any time in the past? (or if easier, within the donation results)

Code:

SELECT [Contribution Data].ID, [Contribution Data].[Last Name], [Contribution Data].[First Name], [Contribution Data].[Address 1], [Contribution Data].[Address 2], [Contribution Data].City, [Contribution Data].State, [Contribution Data].Zip
FROM [Contribution Data]
WHERE NOT EXISTS
(SELECT Donations.ID
FROM Donations
WHERE Donations.ID = [Contribution Data].ID
AND Donations.[Donation Date] > #01/04/07#) ;

(EDIT: I just saw the above posting, will try it after lunch. Thanks again!)

 

[db2]

This example will get you both sides of your request (previously over $100.00 and not since 01/04/2007):

SELECT tblContributors.DonorId
FROM tblContributors LEFT JOIN tblDonations ON tblContributors.DonorId = tblDonations .DonorId
WHERE ( ( Max((tblDonations .DonationAmount)) >= 100 ) AND
( Max((tblDonations .DonDate))<#01/04/2007# ) );

Hi Rookie, thanks for your help so far.

Unfortunately I'm stuck in access 2003 so I have to modify your code to get this:

SELECT [Contribution Data].ID
FROM [Contribution Data] LEFT JOIN Donations ON [Contribution Data].ID = Donations.ID
WHERE ( ( Max((Donations.[Donation Amount])) >= 100 ) AND
( Max((Donations.[Donation Date]))<#01/04/2007# ) );


And what the program gives me is this error:

Cannot have aggregate function in WHERE clause Max(Donations.[Donation Amount]) >= 100 ) AND
( Max(Donations.[Donation Date])<#01/04/2007# )

Any ideas what's going on?

David

 

[MSAccessRookie]

Sorry for two things.

1. Things heated up here and it took a little bit long to reply.
2. The first reply was incorrect.

Try this one. It should be better

SELECT ID, [Donation Amount], MaxDonDate
FROM (
SELECT [Contribution Data].ID, Donations.[Donation Amount], Max(tblDonations.DonDate) AS MaxDonDate
FROM [Contribution Data] LEFT JOIN Donations ON [Contribution Data].ID = Donations.ID
GROUP BY [Contribution Data].ID, Donations.[Donation Amount]
) AS a
WHERE (a. [Donation Amount >= 100 AND a.MaxDonDate > #01/04/2007# );

 

[db2]

I had to fix a couple little label errors but I ended up with this:

SELECT ID, [Donation Amount], MaxDonDate
FROM (
SELECT [Contribution Data].ID, Donations.[Donation Amount], Max(Donations.[Donation Date]) AS MaxDonDate
FROM [Contribution Data] LEFT JOIN Donations ON [Contribution Data].ID = Donations.ID
GROUP BY [Contribution Data].ID, Donations.[Donation Amount]
) AS a
WHERE (a.[Donation Amount] >= 100 AND a.MaxDonDate > #01/04/2007# );

And it seems to work. Does everything look good to you here? Thanks a million for your help Rookie!

 

[db2]

One more question.

Wont this:

a.MaxDonDate > #01/04/2007# );

Give me people whose last donation date was after 01/04/2007? Shouldn't it be:

a.MaxDonDate < #01/04/2007# );

?

 

[db2]

Sorry to keep asking for clarification. Just trying to understand this formula.

So from what I can gather this is giving me a list of all donations over $100 from donors that do not have any donations after the MaxDonDate?

It's not just showing me Donations over $100 before the MaxDonDate even if the donor has donations after this date right?

Because I need to make sure I'm also eliminating any donor that has given over $100 at ANY time, but has already donated ANYTHING after the MaxDonDate.

Ultimately I'm looking for all donors who've given over $100 at any point, but have not made a donation at all after MaxDonDate.

 

[MSAccessRookie]

One more question.

Wont this:

a.MaxDonDate > #01/04/2007# );

Give me people whose last donation date was after 01/04/2007? Shouldn't it be:

a.MaxDonDate < #01/04/2007# );

?

OK, you are right about the ">" vs "<". When I rewrote your query to test it here I had to reverse the logic and I forgot to change it back.

 

[MSAccessRookie]

The query that I originally provided was supposed to:

Find anyone who had ever donated $100 or more [ Max((Donations.[Donation Amount])) >= 100 ) ]

AND

had not Made any donations at all since 01/04/2007 [ Max((Donations.[Donation Date]))<#01/04/2007# ) ]

The query will not return results for donors who have never given $100 or more, and will also eliminate any donors of any amount who donated on 01/05/2007 or later. Therefore, if a donor had donated $150 on 07/04/2007, they would not show up because their donation was after 1/04/2007.

 

[db2]

Ok,

Because in this latest query that you provided it is showing me all the donations over $100 made before the max don date, but some of the donors in that list have donated after the MaxDonDate, it's just not including those donations in the query results. I need these people eliminated too.

Is there some way that I can move the maxDonDate to another place in the query where it will eliminate these donors instead of just eliminating their donations after maxDonDate from the query?

If this is asking too much just let me know that you can't help further. I appreciate your assistance greatly in this matter!

David

 

[MSAccessRookie]

It looks like the Max Donation was being ignored. I built a test database with 10 contributors and 36 donations on 4 separate dates (the final donations being made after 01/04/2007, with one of those people donating over $100 for the first time, and one previous $100+ donor donating less than $100), and ran the following query against it

SELECT ID, MaxDonAmt, MaxDonDate
FROM (
SELECT [Contribution Data].ID, Max(Donations.[Donation Amount]) as MaxDonAmt, Max(Donations.[Donation Date]) AS MaxDonDate
FROM [Contribution Data] LEFT JOIN Donations ON [Contribution Data].ID = Donations.ID
GROUP BY [Contribution Data].ID) AS a
WHERE ( ( a.MaxDonAmt >= 100 ) AND ( a.MaxDonDate < #01/04/2007# ) )

The results were exactly what I think you were looking for. Only the contributors who had given $100 or more, but not more than $100 since 2007, showed up

 

[db2]

It looks like the Max Donation was being ignored. I built a test database with 10 contributors and 36 donations on 4 separate dates (the final donations being made after 01/04/2007, with one of those people donating over $100 for the first time, and one previous $100+ donor donating less than $100), and ran the following query against it

SELECT ID, MaxDonAmt, MaxDonDate
FROM (
SELECT [Contribution Data].ID, Max(Donations.[Donation Amount]) as MaxDonAmt, Max(Donations.[Donation Date]) AS MaxDonDate
FROM [Contribution Data] LEFT JOIN Donations ON [Contribution Data].ID = Donations.ID
GROUP BY [Contribution Data].ID) AS a
WHERE ( ( a.MaxDonAmt >= 100 ) AND ( a.MaxDonDate < #01/04/2007# ) )

The results were exactly what I think you were looking for. Only the contributors who had given $100 or more, but not more than $100 since 2007, showed up

Thanks for all your help Rookie, this is pretty close to what I needed and I've managed to also make several queries that give me the results that I need.

One last question though, you said that this should give me a list of past contributors of $100 or more who have not given $100 or more since the max don date. I actually wanted to eliminate any contributor of $100 or more who has given ANYTHING since the max don date.

Regardless I'm getting close here and have my list. Thanks again so much for your help!