Hello everyone! This forum is extreamly helpful.

I would like to bring value from one table to another, but preserving records that do not have matches in the later.

Example:
Table1 has two columns - ACC and TERR
ACC | TERR
1 | N1
2 | NS
3 | AR

Table2 has one column - ACC
ACC
1
2
3
4
5

I would like to match the TERR column in Table1 to Table2, based on ACC#.
However, I would like to be able to see the full list of account in the resulting query.

The resulting query should look like:
QueryT1T2
ACC | TERR
1 | N1
2 | NS
3 | AR
4 |
5 |


Please help!

Hi JoeBB,

This is what a LEFT OUTER JOIN is used for.

Try

SELECT t2.ACC, t1.TERR
FROM Table2 t2 LEFT OUTER JOIN Table1 t1 ON t2.ACC = t1.ACC;

HTH.

Malc.

Hi Malcolm! Thank you for the reply.

Would you please explain where I should place this expression in and how dod I do that?
Thanks!

Placed it in the SQL: Open new query in design view> go to View> SQL >

SELECT T2.ACC, T1.TERR
FROM T2 LEFT JOIN T1 ON T2.ACC = T1.ACC;

It auto build table relationship between tables in access 2003. My understanding is that this is not permanent relationship and is usually not visible in access 2007 and up.


Additionally, it is important to note that we have to delete any existing relationships between table T1 and T2 to execute the query above.
Could you confirm this, Malcolm?

Works with little name modifications!
You are a genious!
Thank you so much!
Thanks sent!