Howdy folks!
Have a bit of a problem...developed a database for a client, works great.
At some point they decided to back up all of their data, and unfortunately they were simultaneously using the original database as well as their backup for a few months.
I need a way to merge the duplicate's master data table with the current database's. It is 255 columns by about 850 rows/records. The primary key for each record is a unique ID that is not generated by my database.
I've tried using Access' append function, but it throws out duplicate primary key errors for all records. It seems the append function will only append completely new records, which is useless - data is constantly being added to each record, so this function is not sophisticated enough apparently.
The next thing I tried was a union query. Unfortunately, the ~3000 character SQL statement automatically generated by Access has incorrect syntax. I tried to google the error that it spat out, which seemed to be related to the field names containing spaces, but even after I removed spaces from every field name of both tables and tried it again I experienced the same error.
The latest attempt was exporting both tables as excel spreadsheets to try and use excel macros for combining. I haven't had any luck on this end as of yet, plus with this method I would have the lovely task of trying to reimport the table with all 255 columns being correct data types, some of which are "memo" and contain paragraphs of text.
So basically, very simply:
-Have two tables, same columns, same data types
-For any empty cell in the original table, if the duplicate's table has any data there for the corresponding record, copy it
and yet this seemingly simple task is proving to be quite difficult.
Using Access 2007 on a compatibility mode Access 2003 DB.
Any suggestions are GREATLY appreciated! Thanks!!
Have a bit of a problem...developed a database for a client, works great.
At some point they decided to back up all of their data, and unfortunately they were simultaneously using the original database as well as their backup for a few months.
I need a way to merge the duplicate's master data table with the current database's. It is 255 columns by about 850 rows/records. The primary key for each record is a unique ID that is not generated by my database.
I've tried using Access' append function, but it throws out duplicate primary key errors for all records. It seems the append function will only append completely new records, which is useless - data is constantly being added to each record, so this function is not sophisticated enough apparently.
The next thing I tried was a union query. Unfortunately, the ~3000 character SQL statement automatically generated by Access has incorrect syntax. I tried to google the error that it spat out, which seemed to be related to the field names containing spaces, but even after I removed spaces from every field name of both tables and tried it again I experienced the same error.
The latest attempt was exporting both tables as excel spreadsheets to try and use excel macros for combining. I haven't had any luck on this end as of yet, plus with this method I would have the lovely task of trying to reimport the table with all 255 columns being correct data types, some of which are "memo" and contain paragraphs of text.
So basically, very simply:
-Have two tables, same columns, same data types
-For any empty cell in the original table, if the duplicate's table has any data there for the corresponding record, copy it
and yet this seemingly simple task is proving to be quite difficult.
Using Access 2007 on a compatibility mode Access 2003 DB.
Any suggestions are GREATLY appreciated! Thanks!!