Combining a Select query and an Update query

[Guest]

Hi all,

Working with MS-Access 2003, I want to perform an update query that draws
it's data from a select query.
There are 3 tables:
-SoloData
-Components
-CRList

The SoloData-table is linked to the Components-table.
I want to update fields of records within the Solodata-table, but only those
of which a linked Componentfield has a specific value.

The update itself may only happen when a certain field in Solodata equals a
field in CRlist, the SoloDatafield gets updated to the value of the adjacent
field in CRList.

The separate queries run ok:

Select-query:

SELECT SoloData.SymbolNr
FROM Components INNER JOIN SoloData ON Components.SoloDataLink =
SoloData.SoloDataLink
WHERE (((Components.DrawingID) Like "*1"));

Update-query:
UPDATE SoloData Inner JOIN crlist on SoloData.SymbolNr = crlist.hwnummer
SET SoloData.symbolnr = crlist.identnummer
WHERE [SoloData].symbolnr = crlist.hwnummer

But I want to combine them.
What is the best way to accomplish this ?

 

[pietlinden]

Hi all,

Working with MS-Access 2003, I want to perform an update query that draws
it's data from a select query.
There are 3 tables:
-SoloData
-Components
-CRList

The SoloData-table is linked to the Components-table.
I want to update fields of records within the Solodata-table, but only those
of which a linked Componentfield has a specific value.

The update itself may only happen when a certain field in Solodata equals a
field in CRlist, the SoloDatafield gets updated to the value of the adjacent
field in CRList.

The separate queries run ok:

Select-query:

SELECT SoloData.SymbolNr
FROM Components INNER JOIN SoloData ON Components.SoloDataLink =
SoloData.SoloDataLink
WHERE (((Components.DrawingID) Like "*1"));

Update-query:
UPDATE SoloData Inner JOIN crlist on SoloData.SymbolNr = crlist.hwnummer
SET SoloData.symbolnr = crlist.identnummer
WHERE [SoloData].symbolnr = crlist.hwnummer

But I want to combine them.
What is the best way to accomplish this ?

I'm just obscenely lazy... so
cheat and use something like

UPDATE SoloData
SET SoloData.SymbolNr = CrList.IdentNummer
WHERE ... <--- put all your LINKS and FILTERS here.

Just use
WHERE SomeField IN (SELECT ...)

and you should be set.