I need to archive membership records and intend to use an Append Query to copy records to a new Archive Table. I have a Members Table and a Subscription Payments Table which seems the most appropriate table to use. This has four relevant fields: MEMBER_NO, FOR_YEAR, AMOUNT, PAID_ON.
The table records every payment made by each member so that a single member may have several rows representing each year of payment.
I want to select all records where the PAID_ON date is more than 2 years older than today's date. The expression <Date() - 730 seems to handle this ok. My problem is how to exclude those records of members who meet this criteria but who also have payment dates last year and this year.