How to combine two tables in one table such that values are neither duplicated nor missed?
please, see the attached screen shot that show my question
i wanted to join Table5 and table6 such that the values of Field1 are all populated in the resulted table but neither
duplicated nor overlapped!
How can i perform this kind of combination?
thank you in advance,
(66.0 KB, 0 views)
Reply With Quote
10-28-2011, 06:39 AM
Windows XP Access 2010 32bit
Join Date Mar 2011
Location Tulsa, Oklahoma.
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:
3. Change the SQL in the same SQL pane so it looks like this:
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.