Solved

SQL to Update field from another table

Posted on 2013-01-21
11
423 Views
Last Modified: 2013-01-22
I need a query to update the LastPurchase date with the latest record for that vendor from another table.

Table1 - tbl_Vendors:
Fields - VendorID, LastPurchaseDate

Table2 - VendorID
Field - DateReceived
0
Comment
Question by:cmccurdy
11 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
ID: 38802096
I believe this should handle it

update table1 a
set LastPurchaseDate = (select max(DateReceived) from table2 b where a.VendorId = b.VendorId)

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38802122
0
 

Author Comment

by:cmccurdy
ID: 38802197
sjwales - The query you gave errors with "Operation must use an updateable query"

angelIII - Thank you for the link, but my problem now is time, or the lack of, which is why I posted this as a question instead of researching why my query didn't work.
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 35

Expert Comment

by:johnsone
ID: 38802237
What database are you using?
What version of the database?
What is your query?
What is the error you are getting?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38802254
Is it something with ASP?

Found this in the MS Knowledge Base: http://support.microsoft.com/kb/175168
0
 

Author Comment

by:cmccurdy
ID: 38802820
Sorry,  I thought I had posted this in the Access area.

I'm using Access 2010
0
 

Author Comment

by:cmccurdy
ID: 38802935
No, it's not related to ASP

I running Access 2010 and querying a local native database.

The query that gives the error is:

update tbl_Vendors a
set  LastPayment = (select max(DateReceived) from tbl_Parts b where b.VendorId = a.Id)
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38803573
The error appears to be an Access error, which is outside my expertise - hopefully one of the Access experts will chime in.
0
 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 250 total points
ID: 38805791
This has to do with a stupid limitation of Access queries that don't allow you to update data when an aggregation exists in the query.

Read my paper on Dealing with Non-Updateable Microsoft Access Queries and the Use of Temporary Tables
http://www.fmsinc.com/MicrosoftAccess/query/non-updateable/index.html

Hope that helps. It's part of our Microsoft Access Query Help Center: http://www.fmsinc.com/MicrosoftAccess/query/help-center.html
0
 

Author Closing Comment

by:cmccurdy
ID: 38805940
Thanks everyone.  What I was trying to do with a single query apparently can't be done in Access.  I've split the points because sjwales's solution would have worked if it wasn't access and LukeChung-FMS showed me why it wouldn't work and gave the work arounds.<br /><br />I appreciate the help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 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