Merge tables

[Bala]

Gurus,

I have 3 tables ( say Table A, B and C ) and the Vendor number is the common field for all the 3 tables.
Now I would like to merge all the 3 tables and make the table D ( it should contain all the deltails of the vendor number in a single record/row).

Please help me. How do I do it ?

-Bala

 

[Norie]

Do you mean that you want a table consisting of Vendor numbers alone?

Or is it that you want for each Vendor the corresponding fields from all the tables?

For the second option just create a query based on all three tables, link the Vendor numbers, chose the Vendor number from one of the tables and choose all of the fields from each query.

I would first make this as a select query to check it was doing what you wanted.

If it is then turn it into a make table query.

Here is what the SQL might look like for the select query:

SELECT TableA.VendorID, TableA.Field1, TableA.Field2, TableB.Field1, TableB.Field2, TableC.Field1, TableC.Field2
FROM (TableA INNER JOIN TableB ON TableA.VendorID = TableB.VendorID) INNER JOIN TableC ON TableA.VendorID = TableC.VendorID;

and for the make table query:

SELECT TableA.VendorID, TableA.Field1, TableA.Field2, TableB.Field1, TableB.Field2, TableC.Field1, TableC.Field2 INTO TableDFROM (TableA INNER JOIN TableB ON TableA.VendorID = TableB.VendorID) INNER JOIN TableC ON TableA.VendorID = TableC.VendorID;

 

[Mudface]

You could do this via a Make Table query. For example, if you had fields named Col1, Col2 and Col3 in tables A, B and C respectively, the following SQL would create a table with the columns VendorID, Col1, Col2 and Col3 as fields: -

SELECT A.VendorID, A.Col1, B.Col2, C.Col3 INTO D
FROM (A INNER JOIN B ON A.VendorID = B.VendorID) INNER JOIN C ON A.VendorID = C.VendorID;

To do this, create a new query and add tables A, B and C to it and select the fields you wish to see in the new table (only add the VendorID or Vendor number field once to the query). Click on the Query Type button and change it to a Make Table query, then simply run the query to create your new table.