Best way to update records from one table to another (1 Viewer)

MilaK

Registered User.
Local time
Yesterday, 19:27
Joined
Feb 9, 2015
Messages
285
Hello,

I'm using the following Update query to update two fields form tbl_cnv_qry table to tbl_CNVs table, for records that have the same sample_id.

Code:
UPDATE tbl_CNVs INNER JOIN tbl_cnvs_qry ON tbl_CNVs.ID = tbl_cnvs_qry.ID SET tbl_CNVs.First_Rev = [tbl_cnvs_qry].[First_Rev], tbl_CNVs.Second_Rev = [tbl_cnvs_qry].[Second_Rev]
WHERE (((tbl_CNVs.sample_id)=[tbl_cnvs_qry].[sample_id]));

The query is no longer updating the values for some reason.

I'm attaching a sample Access file Test.zip.

Please suggest how to improve the query so it works.

Thanks,

Mila
 

Attachments

  • Test.zip
    489 KB · Views: 105

plog

Banishment Pending
Local time
Yesterday, 21:27
Joined
May 11, 2011
Messages
11,613
Your query's weird. Why did you JOIN them like that?

You have essentially joined them via 2 different fields, although you accomplished one of them in the JOIN clause and the other in the WHERE clause.

The reason your query doesn't update is because it doesn't find any records that meet your criteria. Just so you know, your criteria requires that the [ID] field in both tables match, as well as the [sample ID] field. No records do that, therefore no records get updated.

Also, your tables have poor field names and don't seem normalized. Can you better explain what this is for? Why must you UPDATE values instead of just writing a regular query? What's the purpose of this database?
 

MilaK

Registered User.
Local time
Yesterday, 19:27
Joined
Feb 9, 2015
Messages
285
I'm using tbl_cnv_qry as a temp table to attach to a form so the user is able to edit data on this form. After the form is closed I use update query to record the updated in the two fields.
 

MilaK

Registered User.
Local time
Yesterday, 19:27
Joined
Feb 9, 2015
Messages
285
I removed the left join [ID] between the tables and it wipe-out all the records with the same sample_id from tbl_CNVs.

Code:
UPDATE tbl_CNVs, tbl_cnvs_qry SET tbl_CNVs.First_Rev = [tbl_cnvs_qry].[First_Rev], tbl_CNVs.Second_Rev = [tbl_cnvs_qry].[Second_Rev]
WHERE (((tbl_CNVs.sample_id)=[tbl_cnvs_qry].[sample_id]));

What should the correct query be? Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:27
Joined
May 7, 2009
Messages
19,169
UPDATE tbl_CNVs INNER JOIN tbl_cnvs_qry ON (tbl_CNVs.[chr:gene] = tbl_cnvs_qry.[chr:gene]) AND (tbl_CNVs.sample_id = tbl_cnvs_qry.sample_id) SET tbl_CNVs.First_Rev = tbl_cnvs_qry.[First_Rev], tbl_CNVs.Second_Rev = tbl_cnvs_qry.[Second_Rev];
 

plog

Banishment Pending
Local time
Yesterday, 21:27
Joined
May 11, 2011
Messages
11,613
I'm using tbl_cnv_qry as a temp table to attach to a form so the user is able to edit data on this form

That makes little sense. Why not just use the original table as the form's source?
 

Users who are viewing this thread

Top Bottom