Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-03-2017, 05:08 AM   #1
MilaK
Newly Registered User
 
Join Date: Feb 2015
Location: United States
Posts: 282
Thanks: 33
Thanked 2 Times in 2 Posts
MilaK is on a distinguished road
Best way to update records from one table to another

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
Attached Files
File Type: zip Test.zip (489.0 KB, 31 views)

MilaK is offline   Reply With Quote
Old 08-03-2017, 05:19 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,348
Thanks: 10
Thanked 2,270 Times in 2,222 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Best way to update records from one table to another

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?
plog is offline   Reply With Quote
Old 08-03-2017, 05:27 AM   #3
MilaK
Newly Registered User
 
Join Date: Feb 2015
Location: United States
Posts: 282
Thanks: 33
Thanked 2 Times in 2 Posts
MilaK is on a distinguished road
Re: Best way to update records from one table to another

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 is offline   Reply With Quote
Old 08-03-2017, 05:39 AM   #4
MilaK
Newly Registered User
 
Join Date: Feb 2015
Location: United States
Posts: 282
Thanks: 33
Thanked 2 Times in 2 Posts
MilaK is on a distinguished road
Re: Best way to update records from one table to another

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
MilaK is offline   Reply With Quote
Old 08-03-2017, 06:05 AM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,383
Thanks: 68
Thanked 2,698 Times in 2,583 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Best way to update records from one table to another

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];
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
MilaK (08-08-2017)
Old 08-03-2017, 06:24 AM   #6
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,348
Thanks: 10
Thanked 2,270 Times in 2,222 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Best way to update records from one table to another

Quote:
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?

plog is offline   Reply With Quote
Reply

Tags
update query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Identify strings into 2nd table's records then update 1st table (kind of Vlookup) SunOffice Modules & VBA 4 05-06-2015 01:02 AM
Update query (table to table) not updating all records expected (AC2007) AOB Queries 3 11-26-2013 07:28 AM
How do i update some table records... shenty Modules & VBA 8 02-05-2009 11:20 PM
update records from one table to another paulevans Tables 2 03-09-2008 03:46 AM
Update certain records in table. LEXCERM General 2 05-08-2005 10:27 AM




All times are GMT -8. The time now is 05:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World