How to combine two tables in one table such that values are neither duplicated nor mi

[jamal numan]

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,

regards

Jamal

[Robeen]

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:

 
   Select Field1
   FROM Table5;

3. Change the SQL in the same SQL pane so it looks like this:

 
   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.

[jamal numan]

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:

 
   Select Field1
   FROM Table5;

3. Change the SQL in the same SQL pane so it looks like this:

 
   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.

Thank you for the answer. i works like a charm!

I've here just a little question: can we add to the resulted query a new field that of the type "AutoNumber" and then to convert the query to a table?


very much appreciated

regards

Jamal

[Robeen]

One thing you CAN do is to create an Empty Table with all the fields that your query will have - and then add an AutoNumber field to the empty Table.

Now - instead of making your query a MakeTable Query - you can make it an append query.
When you run the append query - it will add all your rows of data to the empty table and Access will automatically put incremental numbers in the AutoNumber.

Will that work for you?

If you HAVE to create a new table each time - I'm not sure how you would put the autonumber field into the make table query. But there might be a way in Access. I just don't know it.

[jamal numan]

One thing you CAN do is to create an Empty Table with all the fields that your query will have - and then add an AutoNumber field to the empty Table.

Now - instead of making your query a MakeTable Query - you can make it an append query.
When you run the append query - it will add all your rows of data to the empty table and Access will automatically put incremental numbers in the AutoNumber.

Will that work for you?

If you HAVE to create a new table each time - I'm not sure how you would put the autonumber field into the make table query. But there might be a way in Access. I just don't know it.

Thank you Robeen for the answer. these notes are quite helpful.

regards

Jamal