Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-19-2016, 08:22 AM   #1
richardw
Newly Registered User
 
Join Date: Feb 2016
Location: France
Posts: 48
Thanks: 7
Thanked 3 Times in 3 Posts
richardw is on a distinguished road
Update one table with data from another

Hi All,

I have two tables: table 1 and table 2. I have the same set of data in each one.

As a data I have team, type, month, year and averageValue

I'd like to know how do I run an sql update query that can update Table 1 with Table 2's averageValue using team, type, month & year as a criteria?

Thank you in advance

richardw is offline   Reply With Quote
Old 05-19-2016, 09:22 AM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,942
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Update one table with data from another

Update existing records or append new ones?

If you really meant "UPDATE" then you might wish to look into doing an UPDATE query that does an INNER JOIN between the common fields. However, before we can give you a really good answer, you need to clarify which of these fields are keys to your tables. PART (not all) of your solution would resemble

Code:
UPDATE TBL1 INNER JOIN TBL2
ON (TBL1.TEAM = TBL2.TEAM) AND (TBL1.TYPE = TBL2.TYPE) ....
SET TBL1.AVGVALUE = TBL2.AVGVALUE, ...
Beware of using TYPE as a field name because this is also a common property name and Access treats it like a reserved word. Using reserved words as field names usually leads to much grief.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-20-2016, 01:34 AM   #3
richardw
Newly Registered User
 
Join Date: Feb 2016
Location: France
Posts: 48
Thanks: 7
Thanked 3 Times in 3 Posts
richardw is on a distinguished road
Re: Update one table with data from another

Hello The_Doc_Man,

Thank you for your response, I resolved it and my solution was the same as yours.

The Type field is just an exemple for the forum, thank you for the advice

richardw is offline   Reply With Quote
Reply

Tags
access 2007 , sql , update query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to update data from one table to another table using form. access 2010 prabhus Forms 1 12-16-2013 05:45 PM
how to update a filed of table A by entirng data on Table B armankln Tables 1 09-19-2012 07:23 AM
Improve a Function running time - Update data in a table(looping thru the data) kfschaefer Modules & VBA 17 12-09-2010 11:49 AM
Update table with data not in data input form SwanseaAccess Forms 1 10-05-2010 06:00 AM
Update Query: Replace data with data from other table ggodwin Queries 3 03-14-2009 06:56 PM




All times are GMT -8. The time now is 10:26 AM.


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