Combining Records into one table

[PROXI]

I have 2 tables, Collateral_shipped and New_main_info. I have 3000 records in the new_main_info table and about 500 in the collateral. I want to merge the 2 tables together. I have information in the colateral table that would fill in the blanks of the new_main_info. Right now it just deletes the record from the new_main_info instead of just adding data to it. How can I make a query to do this?



Thanks,

PROXI

 

[dhookom]

Do you want to:
1) update existing records in the New_Main_Info table or,
2) append new records to New_Main_Info from the Collateral table

If you want to update, is there a common field value in both tables and is it a primary key?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[p27br]

if you have two tables with the same fields in the same order, then you need an union query :

select * from table1
union
select * from table2

 

[PROXI]

I want to fill in the gaps on the new_main_info table with information that is on the collateral table. Some records are on the collateral table but not on the new_main_info. I would just want those to add to the end of the table.

Hope that helps to clarify

Thanks,

PROXI

 

[PROXI]

and the fields are the same name but there are not the same amount of fields in collateral as in new_main_info. There are around 30 fields in new and about 5 in collateral.

Thanks,

PROXI

 

[p27br]

I think the easiest way to do it is to run the "Find unmatched" query wizard, then open the query in design mode, change it to an APPEND action query, and add the new fields to the query with null values.

 

[p27br]

on second thoughts that won't work , sorry about that

 

[dhookom]

Apparently you want to both append new records and update existing records. Again, could you answer "is there a common field value in both tables and is it a primary key?"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.