There may be a simpler way, but try this:
Create a new query
1. Select Field1 from Table5.
2. Got to View -> SQL View.
You should see something like:
Code:
Select Field1
FROM Table5;
3. Change the SQL in the same SQL pane so it looks like this:
Code:
Select [Table5].[Field1]
FROM Table5
UNION
Select [Table6].[Field1]
FROM Table6
ORDER BY [Table5].[Field1];
If you run this - you will get all the field1 values - no duplicates.
4. Save the Query and name it, for example, Query1.
5. Create a new Query.
6. Select - in this order: Query1, Table5 & Table6.
7. Join Field1 of Query1 to Field1 of Table5 & Field1 of Table6.
8. Right-click the Join lines and make the Join Properties of both option 2.
You should see an arrow pointing towards Table5 & Table6 on the join lines [in your new Query].
10. Select Field1 from Query1, Field2 from Table5 and Field3 from Table6.
11. Run the Query to verify that you get the results you need.
12. If you really need it to be a new table - convert Query1 into a MakeTable Query and run it to create your new table.
There may be a more sophisticated [a single query] way of creating the resultset [crosstab query, perhaps??] but I do not know off hand how to do that.
I hope this helps.