How to combine two table together?

[spheresuper]

I have two table with one same/similar field.

Eg:
table 1
WorkUnit Student
1 julie
2 jack
3 peter


table 2
WorkUnit Teacher
1 Jean
3 Dan
4 Chris

I want to combine table 1 and table 2 as table 3
so table 3 will be
WorkUnit Student Teacher
1 julie Jean
2 jack
3 peter Dan
4 Chris

I try to use append query, but it seems that I can not have duplicates for workunit. Thanks a lot for the help

 

[MSAccessRookie]

I have two table with one same/similar field.

Eg:
table 1
WorkUnit Student
1 julie
2 jack
3 peter


table 2
WorkUnit Teacher
1 Jean
3 Dan
4 Chris

I want to combine table 1 and table 2 as table 3
so table 3 will be
WorkUnit Student Teacher
1 julie Jean
2 jack
3 peter Dan
4 Chris

I try to use append query, but it seems that I can not have duplicates for workunit. Thanks a lot for the help

I cannot understand why you would need to join them, but I believe that an Append Query that is based on a Union query will give you what you want.

UNION QUERY:

Select Table1.WorkUnit, Table1.Student, Table2.Teacher
From Table1 Left Join Table2 On Table1.WorkUnit = Table2.WorkUnit
UNION
Select Table2.WorkUnit, Table1.Student, Table2.Teacher
From Table2 Left Join Table1 On Table2.WorkUnit = Table1.WorkUnit

The First Select will get all records from the Students Table Matching them with Teachers if Teachers exist, an Nulls if Teachers do not exist.

The Second Select will get all records from the Teachers Table Matching them with Students if Students exist, an Nulls if Students do not exist.

The UNION Statement will join them together in one recordset, and remove any duplicates. You may want to add a sort if you have a preferred sort order.

 

[spheresuper]

Thanks a lot. That works