RSayadi
asked on
Matching columns from two tables
Hi,
I need to do the following in MS Access:
Match TABLE_A.[COLUMN_A] with TABLE_B.[COLUMN_A]
Every time there is a match, the value of of TABLE_B.[COLUMN_B] needs to be imported into TABLE_A.[COLUMN_B]
It's important that the value required is set into the first table and not just in a different query.
Can someone help out. Thank you in advance.
I need to do the following in MS Access:
Match TABLE_A.[COLUMN_A] with TABLE_B.[COLUMN_A]
Every time there is a match, the value of of TABLE_B.[COLUMN_B] needs to be imported into TABLE_A.[COLUMN_B]
It's important that the value required is set into the first table and not just in a different query.
Can someone help out. Thank you in advance.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Try this, using a FROM clause:
Alternatively, using a WHERE clause:
UPDATE TABLE_A
SET TABLE_A.[COLUMN_B] = TABLE_B.[COLUMN_B]
FROM TABLE_A INNER JOIN TABLE_B
ON TABLE_A.[COLUMN_A] =TABLE_B.[COLUMN_A]Alternatively, using a WHERE clause:
UPDATE TABLE_A, TABLE_B
SET TABLE_A.[COLUMN_B] = TABLE_B.[COLUMN_B]
WHERE TABLE_A.[COLUMN_A] =TABLE_B.[COLUMN_A]
If the names are all correct then the sql in my post should work.
If you have changed names then you will have to post your actual code for use to see what the problem is.
If you have changed names then you will have to post your actual code for use to see what the problem is.
ASKER
Yes, your post is correct. I just found out that it's a bit more complicated. I asked my question to soon.
Situation is as follow.
I have 3 tables.
The first one is as follow:
TABLE1.COLUMNA, TABLE1.COLUMNB, TABLE1.COLUMNC, TABLE1.COLUMND, TABLE1.COLUMNE, TABLE1.COLUMNF
All cells contain data
The second one:
TABLE2.COLUMNE, TABLE2.COLUMNF, TABLE2.COLUMNG, TABLE2.COLUMNH
Table 2 is missing records from table 1.
Table 3 contains data that must be updated into table 2 after table 2 is updated with the records from table 1.
Your syntax is correct but that is the last step.
Situation is as follow.
I have 3 tables.
The first one is as follow:
TABLE1.COLUMNA, TABLE1.COLUMNB, TABLE1.COLUMNC, TABLE1.COLUMND, TABLE1.COLUMNE, TABLE1.COLUMNF
All cells contain data
The second one:
TABLE2.COLUMNE, TABLE2.COLUMNF, TABLE2.COLUMNG, TABLE2.COLUMNH
Table 2 is missing records from table 1.
Table 3 contains data that must be updated into table 2 after table 2 is updated with the records from table 1.
Your syntax is correct but that is the last step.
Can you provide some sample data for tables 1 and 2 and what you expect it to look like after the first update, then sample data for table 3 and what you expect it to look like after the second update?
ASKER
I used a INSERT INTO query for the fist part and the second part I used the provided suggestion.
INSERT INTO BASELINE ( COUNTRY, USERNAME, BASELINE_DATE, IN_SCOPE, USER_ACCOUNT_CREATED, HOME_DIR_CREATED, PROFILE_DIR_CREATED, GATE_DIR_CREATED )
SELECT [EUROPE_USERS].[COUNTRY], [EUROPE_USERS].[SAMACCOUNT
FROM EUROPE_USERS
WHERE ([EUROPE_USERS].[INSERT_DA
AND
([EUROPE_USERS].[SAMACCOUN
Thank a lot
INSERT INTO BASELINE ( COUNTRY, USERNAME, BASELINE_DATE, IN_SCOPE, USER_ACCOUNT_CREATED, HOME_DIR_CREATED, PROFILE_DIR_CREATED, GATE_DIR_CREATED )
SELECT [EUROPE_USERS].[COUNTRY], [EUROPE_USERS].[SAMACCOUNT
FROM EUROPE_USERS
WHERE ([EUROPE_USERS].[INSERT_DA
AND
([EUROPE_USERS].[SAMACCOUN
Thank a lot
Umm... you are working with multiple people here. Did you mean to accept Pete's solution rather than mine? From the conversation here, I think you might have mistakenly clicked the "Accept" button under my post.
ASKER
Shoot! Sorry about that!
I am doing 30 migrations in 7 countries and am so busy ATM that the 'll things tend to slip.
I am doing 30 migrations in 7 countries and am so busy ATM that the 'll things tend to slip.
ASKER