Update Query with a join to another query - Help

[ry94080]

Hello,

I'm trying to create an update query that contains a join to another query. Usually you get the error, "Operation must use an updateable query."

i'm trying to avoid creating a temporary table that contains the contents of the query.

I have the query below, but it is not working as expected:

DoCmd.RunSQL "UPDATE tblPharmacyCultures SET tblPharmacyCultures.[Finaled] = True WHERE EXISTS(Select * from vPharmacyAccNo_Finaled WHERE vPharmacyAccNo_Finaled.Accession = tblPharmacyCultures.Accession)"

Any ideas appreciated

 

[arnelgp]

maybe:

DoCmd.RunSQL "UPDATE tblPharmacyCultures 
SET tblPharmacyCultures.[Finaled] = True 
WHERE Exists (SELECT NULL FROM vPharmacyAccNo_Finaled WHERE Accession = tblPharmacyCultures.Accession);"

 

[ry94080]

maybe:

DoCmd.RunSQL "UPDATE tblPharmacyCultures
SET tblPharmacyCultures.[Finaled] = True
WHERE Exists (SELECT NULL FROM vPharmacyAccNo_Finaled WHERE Accession = tblPharmacyCultures.Accession);"

I tried that, but it updates every single record. Which should be the case. It should only update the records that have matching accessions from the vPharmacyAccNo_Finaled query.

 

[arnelgp]

try this:

DoCmd.RunSQL "UPDATE tblPharmacyCultures 
  SET tblPharmacyCultures.[Finaled] = True 
WHERE Accession In (SELECT Accession From vPharmacyAccNo_Finaled)"

 

[Pat Hartman]

Sounds like a schema design problem. Perhaps the field you are trying to update belongs in a different table.

 

[ry94080]

try this:

DoCmd.RunSQL "UPDATE tblPharmacyCultures
  SET tblPharmacyCultures.[Finaled] = True
WHERE Accession In (SELECT Accession From vPharmacyAccNo_Finaled)"

Looks like this works!!! Thanks @arnelgp !

 

[Gasman]

Crossposted https://www.utteraccess.com/topics/2066783?post=unread