Update one table with data from another (1 Viewer)

richardw

Registered User.
Local time
Today, 02:23
Joined
Feb 18, 2016
Messages
48
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 :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:23
Joined
Feb 28, 2001
Messages
27,001
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.
 

richardw

Registered User.
Local time
Today, 02:23
Joined
Feb 18, 2016
Messages
48
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 :):)
 

Users who are viewing this thread

Top Bottom