update a table based upon another table

[Guest]

I need to run a query to update a field in a table with values from another.
I can do this in SQLServer, but when I try it in access, I keep getting an
error message that state I need an updateable query. Any ideas?

update tblServer
set asset_tag = (select CMS_TAG from tblDES_Server where server.asset_tag =
CMS_TAG);

 

[Steve]

A relationship must exist between the tables or you must be able to join the
primary key in one table to a foreign key on the pther table in a qyery.
Create a query that includes both tables. If no reltionship is dosplayed
between the tables, jpin the appropriate fields. You only need the field
from Table 1 to be updated in the query. Change the query to an update
query.

Where it says Update To, put the following expression:
NameOfTable2.NameOfField

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Spencer]

Different syntax

UPDATE tblServer INNER JOIN tblIDES_Server
ON tblServer.Asset_Tag = tblIDES_Server.CMS_Tag
SET Asset_Tag = [CMS_TAG]

Although this makes no sense to me since if AssetTag = CMS_TAG then they are
already equal and therefore why are you setting them equal to each other.

Unless Server is a third table involved in your query. I had assumed that
was a typo in your posting. If that is no typo, then you will probably have
to use one of the VBA functions to get the value you want for the update.
DLookup comes to mind.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..