Merge two tables

[joethall]

How can I merge the records from table B into table A ignoring fields not in A?

The two tables are largely the same but with some significant differences.


I want the resultant Table A records to have only the fields from Table A.

I need to end up with all records from Table A preserved and additional records
created in Table A from Table B where the index fields are different.

[June7]

Run an INSERT action SQL or simply copy/paste. Do you have compound index set to unique? Duplicates will not be allowed, records just won't insert.

[joethall]

I did not expect I could send files but here they are!
tblCrewsArchiev2018 needs to be merged into tblCrewsArchive
retaining only the non-2018 fields.

[June7]

As far as I can see, both tables have the same IHSAID keys for 2018. So you don't need to add new records, but update existing?

I looked at IHSAID 13622 in both tables. Data looks same. Don't know what you mean by 'non-2018' fields. Both tables have same fields. Provide a specific example of what you want to accomplish.

[joethall]

Ooops! I fat-fingered the creation of the zip file I sent earlier and you got two copies of the same tables.
I think that you will now see that the 2018 file has a bunch of fields that are not in the non-2018 table.
CClast_, CCfirst_ are two such fields.

So that you don't have to look it up, I need for the records in the 2018 table to be inserted into the non-2018 table where the IHSAID (primary key for both) is not already present.

For the occurrence of a new IHSAID, only the 2018 fields that match the non-2018 table should be copied. If the IHSAID from 2018 is already present in non-2018 then simply skip that field.

When done, all the records should have only the fields in the non-2018 table.

Thanks

Joe

[June7]

This is far from a normalized data structure and the query will be a very long statement. The field names are not all exactly same. Roughly:

INSERT INTO tblCrewsArchive(IHSAID, <list the fields you want to poplulate>) SELECT IHSAID, <list the fields you want to pull from> FROM tblCrewsArchive2018

Since IHSAID in tblCrewsArchive is set as Indexed no duplicates, no duplicate IHSAID will transfer, entire 2018 record will be omitted.

"If the IHSAID from 2018 is already present in non-2018 then simply skip that field" doesn't really make sense. As already stated, entire record will not transfer.

Alternative is VBA code opening and manipulating recordset objects.

[Marvelick]

I have similar question. I have excel spreadsheet (let’s call it “Excel 1”) And it contains large amount of info : item #, date, contact, po# , notes etc. weekly I add notes to this spreadsheet. Once a week I get new spreadsheet (excel 2) that has updated info. So some rows are added to excel 2 and some rows are removed because they’re resolved. I have to use excel 2 , BUT I need my notes from excel 1 added to excel 2 for the rows that are remaining same. So I need to use excel 2, I need to add notes from excel 1 for rows that are repeating. Currently, I do it with vlookup in excel. But Is there a way to create access database to have this done ?

Just asking if it’s possible - to upload excel 1 today and keep adding notes in column “notes” , then , next Wednesday upload excel 2. By doing so:

1) rows that are repeating in excel 2 should be updated with notes . Column “notes” in excel 2 will be empty, so copy notes from excel 1 to database.

2) rows that do not appear in excel 2, should be removed. So some rows were in excel 1 but not excel 2 , delete them from database.

3) new rows are added to excel 2, they didn’t exist in excel 1, add them to database.

Thank you !!!!!!

[June7]

Marvelick, you should have started your own thread instead of hijacking. Could include a link to this existing thread if think it can be helpful to readers. New unanswered threads get more attention.

Yes, Access is most likely better tool for your situation.

[Marvelick]

Thank you @June7, I apologize , I didn’t know I
should do new thread I connected because I thought it’s similar situation. Thanks much for advice!