Help to write a subquery. (1 Viewer)

[mahenkj2]

UPDATE tblTransaction SET tblTransaction.UnloadingTime = Now(), tblTransaction.Unloadedby = [currentuser]
WHERE (((tblTransaction.BottleFK)=[forms]![frmload].[bottleID]));

I am running an update query based on the form's bottleID field but it update all the record of that bottleID. That bottleFK has many instance in table transaction and I want to update only last entered record. To track the event, I have TransactionID and loading time in tblTransaction.

I assume that I should have subquery in this query, so first I get matched records based on bottleID and then find the latest record.

I have never written a subquery, so I could not make this happen and lot of struggle.

Please advise.

best regards.

 

[mahenkj2]

Is this really a tough one or requires some other solution.

 

[mahenkj2]

Any thoughts please.:banghead:

 

[mahenkj2]

UPDATE tblTransaction SET tblTransaction.UnloadingTime = Now()
WHERE (((tblTransaction.BottleFK)=(SELECT Max(tblTransaction.TransactionPK) FROM tblTransaction HAVING (([bottlefk]=[forms]![frmload].[bottleID])))));

I have tried several times and finally reached up to above lines. Still, this is not working i.e. it always says 0 records to update whereas the subquery statement if used alone works fine.

So I think I still need help of experts.

best regards.

 

[mahenkj2]

UPDATE tblTransaction SET tblTransaction.UnloadingTime = Now()
WHERE (((tblTransaction.TransactionPK)=(SELECT Max(tblTransaction.TransactionPK) FROM tblTransaction HAVING (([bottlefk]=[forms]![frmload].[bottleID])))));

I found why. Thanks all.