Solved

update query not updateable

Posted on 2012-03-22
2
230 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
ID: 37753744
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
ID: 37753935
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

807 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