Last Date

[Guest]

I have a donation table which has the fields; date, donation,amount and last donation. There is also a donor table. How do I populate the last donation field? How can I do a query to find people that have not donated in the last 2 years

Thank
Jeff

 

[Allen Browne]

Do not store the last donation.
Just use a text box with ControlSource of:
=DMax("DonationDate", "tblDonation", "DonorID = " & Nz([DonorID], 0))

To find the people who have NOT donated in the last 2 years, it may be
easiest to use a subquery:

SELECT * FROM tblDonor
WHERE NOT EXISTS (
SELECT DonationID FROM tblDonation
WHERE (DonationDate >= DateAdd("yyyy", 2, Date()))
AND (tblDonationID.DonorID = tblDonor.DonorID) );

If the subquery seems too strange, create a query that does select the
donors from the last 2 years, and then use the Unmatched Query wizard to
combine it with the donors table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a donation table which has the fields; date, donation,amount and

last donation. There is also a donor table. How do I populate the last
donation field? How can I do a query to find people that have not donated
in the last 2 years?

 

[Guest]

Thanks Allen, I will give it a go.