RULE: if XMDATE for a PERSON (Lname | Fname | dob ) has at least on instance equal to or newer than 6 years, then do not ARCHIVE ( example: newer than 10/25/2015)
ID XMdate Last name first name birth date street ARCHIVE_Y_N
1 01/01/1950 banks rob 01/01/1960 1 none No
2 02/02/1955 banks rob 01/01/1960 1 none No
3 03/03/1960 banks rob 01/01/1960 2 moved No
4 08/08/2000 banks rob 01/01/1960 2 moved No
5 09/09/2020 banks rob 01/01/1960 3 another No
6 10/24/2021 banks rob 01/01/1960 3 another No
7 01/01/1950 dunk slam 02/02/1970 23 none No
8 01/02/1950 dunk slam 02/02/1970 23 none No
9 10/24/2000 hearts ace 03/03/1980 435 lane No
ID is primary key autonumber index no duplicates
END Result is: Mr. Dunk & Mr. Hearts are archived, only Rob Banks is a current customer and not archived
This code results in the correct list as desired:
SELECT Max( Plist.ID), max(Plist.xmdate), last(Plist.lname) AS lastname, last(Plist.fname) AS firstname, last(Plist.dob), last(Plist.street), last(Plist.XMDATE) AS PDATE
FROM Plist
GROUP BY Plist.lname, Plist.Fname, Plist.dob
ORDER BY Max(PlisT.XMDATE);
Expr1000 Expr1001 lastname firstname Expr1004 street XMDATE 8 01/02/1951 dunk slam 02/02/1970 23 none 01/02/1951 9 10/24/2000 spades ace 03/03/1980 435 team 10/24/2000 6 10/24/2021 banks rob 01/01/1960 3 another 10/24/2021
So ROB BANKS has had a transaction since 2015 (actually as recent as 2021) so he will not be archived.
QUESTION: How Do I use this result to UPDATE or SET ARCHIVE_Y_N to "YES" IN THE parent TABLE?
i PRESUME SOME SORT OF JOIN IS USED AND PERHAPS IN code builder i would use a SET command
Followup question: Then there is the matter of the children tables provided in the attachment. I have to enforce referential integrity
and SET those flags to YES. Then APPEND them to ARCHIVE first and then DELETE them before doing the same in PARENT
If you can answer these two questions, you are heads & shoulders above your peers in the RESPONDER community; no one has been able to do step up to this challenge so far. Indeed, noone could come up with the code I posted above, so please show some respect this blue collar non programmer type
figured out on my own and just help me with the logic to SET ARCH FLAGS. I think I can handle the APPEND and DELETE. I probably hav e written code ten years ago that would do the SET in Code builder, but a real winner of an advisor could save me a lot of time and grief I am sure. And please don't waste time dwelling on the three fields in PARENT that are not normalized. You save me the insult and I will do the same for you. That issue is besides the point.
TEST archive 1 - Copy.accdb