Solved

update query not updateable

Posted on 2012-03-22
2
228 Views
Last Modified: 2012-03-26
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
0
Comment
Question by:gbnorton
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
Comment Utility
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
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now