How to use reserved word MAX correctly to UPDATE a field

[rogeye]

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

[ranman256]

bad news, you cannot use a summation query (max,count, sum, etc) in an update query.
this woulda made things easier, but you CAN use a function that does the max in the update qry.

function getMax(pvID)
getMax = DMax("[xmdate]", "table","[id]=" & pvID & ")"
end function

the in the query:
update table set [MyFld] = getMax([id])

[NoellaG]

You can insert the results of the summation query into a (new) temp table, use this for the update and then delete the temp data.

[Gicu]

Please review the updated file to see if that is what you're after. As mentioned totals queries cannot be joined in update queries, so if the method I used is too slow due to large datasets then you might want to try Noella's temp table approach (which will create some db bloat that will require some compacting).
I also think that the archive flags in the child tables are not required as you can always identify those records by simply joining the parent table (to do the append\delete queries for archiving).

Cheers,

[rogeye]

bad news, you cannot use a summation query (max,count, sum, etc) in an update query.
this woulda made things easier, but you CAN use a function that does the max in the update qry.

function getMax(pvID)
getMax = DMax("[xmdate]", "table","[id]=" & pvID & ")"
end function

the in the query:
update table set [MyFld] = getMax([id])

WoW, you three out did yourselves. Great feednack, ranman256, NoellaG and Gicu. I need to download Gicu solution and open it tomorrow. I was afraid of downloading files and viruses. I don't know how you guys live with that risk on these platforms, really. But apparently Gicu has put together some code and didn't want to take the time to cut and paste into the reply?

Ranman256: Let me ask you in the meantime. Your solution looks intriguing. I can learn something new here for sure.

The way you wrote the code won't work, but I think I can imagine a way to modify it so it would be slick.
-what is pvID? you mean the autonumber keyfield in PARENT? well we don't want MAXvalue for each autonumber. Get that? That is simply XMdate by definition.

However, we could possibly create a temporary field at run time. A concatenation of Lname | Fname | dob and call it RUNTIMEkey
Then we can get Dmax across RUNTIMEkey!

How do I code for RUNTIMEkey? Is there a concatenation function?

Could you also spell out the udate query more precisely , please - -remember I am Joe Q Public , not a Pro programmer like you three. I do this once every ten years, literally.....literally...don't assume I know what you are talking about

NoellaG approach is what I was thinking all along - - How exactly would I write the summation query so it actuallly INSERTS the result into a (new) temp table instead of to the display? INSERT into TEMP file after the GROUP STATEMENT? SEE what I mean? I am dumb



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)
INSERT TEMPTABLE; <----------------------------------

[rogeye]

WoW, Great feedback,
remember I am Joe Q Public , not a Pro programmer like you three. I do this once every ten years, literally.....literally...don't assume I know what you are talking about

How exactly would I write the summation query so it actuallly INSERTS the result into a (new) temp table instead of to the display? INSERT into TEMP file after the GROUP STATEMENT? SEE Below seewhat I mean? I am dumb



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)
INSERT TEMPTABLE; <----------------------------------

[Welshgasman]

But apparently Gicu has put together some code and didn't want to take the time to cut and paste into the reply?

Wow, some people are so ungrateful

[Minty]

Rogeye went on my ignore list probably quicker than any other poster.
The level of rudeness and obnoxiousness to someone trying to help someone was extra special, glad to see that my judgement wasn't out.

[isladogs]

I stopped putting forum members on an ignore list during the time I was a moderator at another forum. That was because I felt obligated to read any comments no matter how obnoxious.

Congratulations to Rogeye for becoming the first person I've put on my ignore list in at least 4 years.
I agree totally with comments made by other members who have tried to assist and been met with rudeness by way of thanks.
Soon there may be nobody left who is willing to reply to this member.

[rogeye]

For those users who might stumble onto PERFORMANCE SPEED ISSUES whn moving an application
to a new platform. I figured out the answer: The issue is the TRUST CENTER

You have to identify your file folder to ACCESS as trustworthy. If you do not, even if you enable content,
you will dog your system on performance speed each time the FORM I/O/s the table.

Here are the steps to take:


MICROSOFT PIZZA SYmbol
ACCESS OPTIONs
TRUST CENTER
TRUST CENTER SETTINGS
ADD locations: select C:\USER FILE

I want to thank Davegri, Ranman256, NoellaG, Gicu for trying to help me.

I found Minty, Welshgasman, Isladogs to be dismissive if not, condescending or dense.
They really should not be on the community. They have no interest in truly listening to
the problem people are having. I suppose they work well with full time programmer types.
Not everyone who uses ACCESS does it for a living. I admitted I was a hack all along.

And this hack dogged those three. Good for me. I figured out an issue that affects my livelihood. No thanks to those three.


And don't forget about the TRUST CENTER.




Trust me-


ROGEYE

[rogeye]

I figured out the performance issue. No thanks to you. If you are interested to learn something, read my latest post. Doubt you are really interested in helping people enough, to read it.