update query not updateable
Posted on 2012-03-22
I have a table with this data:
TubeNo PartNo ReceivedDate SerialNo JoinedDate
53556 PS512 1/1/12 00065 1/5/12
53556 PS512 3/1/12
64687 PS485 2/1/12
64687 PS485 4/1/12
I have this update query:
UPDATE MoxtekJoinTubes INNER JOIN qry_Update_JoinTubes_To_Assys_2 ON (MoxtekJoinTubes.Part_Number=qry_Update_JoinTubes_To_Assys_2.VmiPartNumber) AND (MoxtekJoinTubes.Tube_Serial_Number=qry_Update_JoinTubes_To_Assys_2.SerialNumber) SET qry_Update_JoinTubes_To_Assys_2.Assy_SerialNumber = [MoxtekJoinTubes].[Assembly_Serial_Number], qry_Update_JoinTubes_To_Assys_2.Date_Joined = Date()
An operator scans in PartNo. Then scans in the group of SerialNo's and TubeNo's. This data goes into a temporary table(MoxtekJoinTubes). When SAVE is selected the TubeData table is updated via the query qry_Update_JoinTubes_To_Assys_2.
The query above updates all records for that TubeNo and PartNo.
But, I only want to update the records with most recent ReceivedDate. In the example above, if the current PartNo is PS512 with TubeNo 53556 then only the record with RecievedDate of 3/1/12 should be updated. The other left alone. If the current PartNo is PS485 with TubeNo 64687 then only the record with RecievedDate of 4/1/12 should be updated.
I created an additional query that using Totals and Last that selected the correct records. But Access won't let me use it with the Update query saying it is not updateable.
I need help creating a query that selects the records with the most recent RecievedDate that can be used with an Update query.