Combining two tables with respect to each unique record's individual data cells

[hunterw]

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. I'm using Access 2007, but the database is the compatibility mode so it works with 2003. Any suggestions are GREATLY appreciated! Thanks!!

 

[wiklendt]

it also sounds like you don't have normalised data.

to start: it sounds like you have JUST ONE table in this database? (plus another in the 'backup' database). is one of your fields in this one table an autonumber and unique? if so, then your backup version will have some of the same autonumber being used for different records. i suspect this is the one that's causing you append errors. what you should try is to append every field EXCEPT the autonumber PK (this will, i think, be automatically added as each new record is appended from the other DB).

THEN we have to have a SERIOUS discussion on normalisation (and paragraphs). 255 columns? one table? serious issues there....

 

[hunterw]

Negative - the primary key is unique but is not autonumbered. The IDs are generated outside of this database. There are a minimal amount of non-duplicate records on the tables - so few that I could probably match them by hand. The only reason I used a single table was because all 255 columns of data are used on a single form and report, and without lots of convoluted VB it seems like you could only assign one table to a form in the design view. Trying to append everything other than the primary key is an interesting idea to try - thanks!

 

[wiklendt]

without lots of convoluted VB it seems like you could only assign one table to a form in the design view

negative - you can have more than one table in a form in one of two ways that i know about:

(1) bound... or (2) unbound form...

...with subforms. each subform is based on each one table/query. these can be either independant (i.e., on an unbound form) or linked back to the main form/table (bound form).

 

[The_Doc_Man]

From your description your data is NOT normalized. We would be hard-pressed to make this work with normalized data. I'm not going to say it would never work with non normalized data, but it would be nightmarish.

If you haven't done so already, look up "Database Normalization." In 35+ years of work with varying databases, I've never seen one that needed more than about 40 fields in a single table, and in that case, at least 20 of them were foreign keys.