Update on Max(date)

[Guest]

Table BOX has four relevant fields: order, box_id, ship_date, and trackno.
Table UPS has two fields: TrackNum and OrderRef.

I need to update the trackingno field in the box table by inserting the
value found in the tracknum field of the UPS table. (Note order=OrderRef)

My problem is that there are several boxes in each order and I need to enter
the tracking number to only one box. (usually the most recent)

How can I select the max(ship_date) and still update?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps (Access SQL - untested):

UPDATE DISTINCTROW Box AS B INNER JOIN UPS AS U ON B.order = U.OrderRef
SET trackno = U.TrackNum
WHERE B.ship_date = (SELECT MAX(ship_date) FROM UPS WHERE OrderRef =
B.order)

This may not work - 'cuz Access doesn't like "not updateable" queries
and this may be one; also, if there are more than one box_id on the same
ship_date, each of those rows (records) in Box will get the UPS.OrderRef
value.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQZwRMoechKqOuFEgEQIrJgCeJvVgVH56DLAseCy0P4YYw6NaBQIAn2uD
tfBdJSMBRer85wSKdCCA6B6S
=Zzpi
-----END PGP SIGNATURE-----

 

[John Vinson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps (Access SQL - untested):

UPDATE DISTINCTROW Box AS B INNER JOIN UPS AS U ON B.order = U.OrderRef
SET trackno = U.TrackNum
WHERE B.ship_date = (SELECT MAX(ship_date) FROM UPS WHERE OrderRef =
B.order)

This may not work - 'cuz Access doesn't like "not updateable" queries
and this may be one; also, if there are more than one box_id on the same
ship_date, each of those rows (records) in Box will get the UPS.OrderRef
value.

In fact it almost certainly won't work. Even in cases like this where
it logically SHOULD work, Access considers the query non-updateable.

The getaround is to use the DMax() function instead:

UPDATE DISTINCTROW Box AS B
INNER JOIN UPS AS U ON B.order = U.OrderRef
SET trackno = U.TrackNum
WHERE B.ship_date = DMax("ship_date","UPS","OrderRef =" & B.order)

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John,
Your solution works... however, I had to make a few changes due to apparent
confusion on the location of fields. My code is as follows:

UPDATE DISTINCTROW Box AS B
INNER JOIN UPSIN AS U ON B.order = U.OrderRef
SET B.trackno = .[TrackNum]
WHERE (((B.ship_date)=DMax("ship_date","Box","order=" & .[OrderRef])));

Thanks for your help!