update query not updateable

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()
WHERE (((MoxtekJoinTubes.Computer)=[Forms]![frmJoin_Tube_Moxtek]![txtComputer]));

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.

Thanks,
Brooks
gbnortonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Any query which involves a GroupBy or summary is deemed not updateable by Access.

You can see if you can use a DMax() expression instead of your totals query.
If that is not possible, then you will have to write the summaries into a table and update from that table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
Also,

Queries are virtual tables.  Upon occasion, if you save out a select query that presents the right records and give it a name (qrySomeRecords) you can get the update query

UPDATE qrySomeRecords SET qrySomeRecords.SomeField = someValue;

to work.  The key, of course, is that the Totals field will for sure not be updateable.
It's worth trying!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.