?
Solved

update query not updateable

Posted on 2012-03-22
2
Medium Priority
?
239 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 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

621 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