The UNION operator will not match fields from table. It will only append the result of the second SELECT to the first.

For example, if you have record A, B and C in the OLD_TABLE and C, D and E in the NEW_TABLE, you will get A, B, C, D, E if you use the UNION (or A, B, C, C, D, E if you use UNION ALL).

I think that what you need is a JOIN:
SELECT *
FROM Original_table AS O
INNER JOIN New_table AS N
ON N.JoinedField = O.JoinedField

Notice that you will need a field (JoinedField - normally a primary key) that has the same value in both table to be able to join them.


>Good day everyone!
>I needed to add more fields to a table in Microsoft Access but could not change the table properties so I created a separate table with the additional fields that I need and a one to many relationship from the Original_table to the New_table so that the join would include all records from the Original_table and only those records from the New_table where the joined fields are equal. I've got two forms to work with. The first form corresponds to the Original table and the second form corresponds to the New table. On both forms I use an adodc connection corresponding to the appropriate table. Why don't the text box fields change in my new form to reflect the record that is displayed in the original form? Do I need to use a SQL statement like
>
>SELECT * FROM Original_Table
>UNION ALL SELECT * FROM New_Table
>
>for both adodc RecordSource properties instead of just : SELECT * FROM Original_Table
>for the original form and: SELECT * FROM New_Table for the new form?
>
>Any help will be greatly appreciated!
>Thank you!
>Max