Moving fields from one table to another

[Guest]

I have 2 fields in a sub table I need to move them to the main table with
their contents.

Is this possible?

Thanks

 

[John Vinson]

I have 2 fields in a sub table I need to move them to the main table with
their contents.

Is this possible?

Thanks

With several steps, yes. You would need to go into the parent table;
add the new field; create a Query joining the two tables; change it to
an Update query, and update the new field to the value in the child
table; finally delete the field from the child table.

Note that if you have multiple records in the subtable and they have
DIFFERENT contents in the field, there's no way to do this (as the
question was posed). You'll need to decide which of the values is the
one you want.

John W. Vinson[MVP]

 

[fredg]

I have 2 fields in a sub table I need to move them to the main table with
their contents.

Is this possible?

Thanks

Back up your data first.

Add 2 new fields to the main table.
Then run an update query:

Add both tables to the query grid using the Show Table dialog.
Make sure the join is correct.

Then drag the SubTable fields onto the grid, Then the new MainTable
fields. Then in the new field's Update To: line write the SubTable
table and field name. When done, the SQL should look something like
this (with your actual table and field names):

UPDATE MainTable INNER JOIN SubTable ON MainTable.ID = SubTable.ID SET
[MainTable].[AddedField1] = [SubTable].[Field1],
[MainTable].[AddedField2] = [SubTable].[Field2];

Verify that the update is done correctly, then delete the unwanted
fields from the subtable.