Results 1 to 2 of 2
  1. #1
    New Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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:
    SELECT *
    FROM Member
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts