Results 1 to 2 of 2
2004-06-11, 16:48 #1
- Join Date
- Mar 2004
- Houston, Texas, USA
- Thanked 0 Times in 0 Posts
Date calculation in Not In statement (2000/XP)
I'm working with 2 tables -- Members and Donations. In the Members table I want to add a Status field that will be based on how long it has been since the member made a donation. Anyone who has not made a contribution in the past year would be "Lapsed," those who have contributed in the past year would be "Active."
I hope I am on the right track. I thought I should write a SQL query with a Not In statement, and use that in the Status field's Validation Rule?. What I did write did not work, and I don't know if it is because it is written incorrectly or because it cannot be done. Here it is:
SELECT Member.MemberID, Member.Status, Member.LastName, Member.FirstName, Donations.DonationID, Donations.PostDate
FROM Member INNER JOIN Donations ON Member.MemberID = Donations.MemberID
WHERE (((Member.MemberID) Not In (SELECT MemberID from Donations where PostDate >Date()-365));
How off base am I?
2004-06-11, 19:12 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Date calculation in Not In statement (2000/XP)
1. It is not necessary to create a Status field in the Members table, since you can run a query at any moment to find out who has (or has not) donated in the last year. If you do create and fill a status field, you would have to update it regularly. It would be painful if you sent somebody an incorrect reminder.
2. The query you posted won't change the status field, it just selects members. You don't need to include the Donations table in this query itself, you just need it in the WHERE part.
3. You can use DateAdd to calculate the date a year ago. That is more accurate than Date()-365 (this year is a leap year, for instance.)
This query returns all members who have NOT donated in the past year:
WHERE MemberID Not In (SELECT MemberID FROM Donations WHERE PostDate >DateAdd("yyyy", -1, Date()))
Omitting Not returns all members who have donated in the past year.