Merging Data (Access97)

Topic

New Reply

Does anyone have any suggestions about the best way to combine data from several different tables from one database into another? The situation came about because a desktop shortcut was deleted and a new one created, but the new shortcut pointed to the wrong file. In the meanwhile data was being entered in 2 separate places, which means that neither has all the information it should. The layout (tables, queries, etc.) of both files are identical, only the data is different. Is there a relatively simply (fingers crossed) way of taking the information from one and importing it into the other? Thanks for your help!

Reply | Quote

Replies

There is no clearcut recipe for this kind of problem. If you want to do it yourself, you can create links in the database you want to keep to the tables in the other database, and create queries to find the differences. You can use the Find Unmatched Query Wizard to help with this. If you have a “date modified” field in the tables, that would make things easier, but in the end, a certain amount of manual checking is inevitable.

There are third-party utilities that can help synchronize databases, for example CDB Database Comparator.

Reply | Quote

There is no clearcut recipe for this kind of problem. If you want to do it yourself, you can create links in the database you want to keep to the tables in the other database, and create queries to find the differences. You can use the Find Unmatched Query Wizard to help with this. If you have a “date modified” field in the tables, that would make things easier, but in the end, a certain amount of manual checking is inevitable.

There are third-party utilities that can help synchronize databases, for example CDB Database Comparator.

Reply | Quote

The information that has been put into the 2 locations…is it along the lines of creating new records with information? Or editing existing info? You may want to muddle with making copies of the two databases and running update or append queries…update queries if the info has just been changing and you can like them by assigning identical primary keys or append queries if new records have been created. If you’re just trying to bring in information onto one “table” from many, I’m assuming they all relate to one specific thing (a person, a company, etc..) and that person or company should have a primary key. You could then create a query pulling the information from the linked tables….I’m not at all familiar with the abilities of Access 97, I don’t know what features it has or doesn’t have, but assuming it can do all of the above, I’d try some of those options… but like Hans said, without a date modified cell or some other identifier that says “HEY! I’ve been changed!”, there is definitely going to be some manual checking…if only one table has been changed by one source, and another table has been changed by another source, then an update query would answer your problems….if everyone’s been muddling in everything, then everyone *might* be a little upset when you try to return to one database.

Reply | Quote

Thanks guys for your responses. I thought as much, but had to ask. I’ll definitely make a copy of the database before trying anything. The database contains patient data so I’m thinking just a couple of tables were probably changed (new patients added: name, dob, etc.) and details regarding their visit to the hospital. So perhaps appending the missing data may work. I’ll let you know how it turns out & thanks again for your suggestions.

Reply | Quote

Thanks guys for your responses. I thought as much, but had to ask. I’ll definitely make a copy of the database before trying anything. The database contains patient data so I’m thinking just a couple of tables were probably changed (new patients added: name, dob, etc.) and details regarding their visit to the hospital. So perhaps appending the missing data may work. I’ll let you know how it turns out & thanks again for your suggestions.

Reply | Quote

The information that has been put into the 2 locations…is it along the lines of creating new records with information? Or editing existing info? You may want to muddle with making copies of the two databases and running update or append queries…update queries if the info has just been changing and you can like them by assigning identical primary keys or append queries if new records have been created. If you’re just trying to bring in information onto one “table” from many, I’m assuming they all relate to one specific thing (a person, a company, etc..) and that person or company should have a primary key. You could then create a query pulling the information from the linked tables….I’m not at all familiar with the abilities of Access 97, I don’t know what features it has or doesn’t have, but assuming it can do all of the above, I’d try some of those options… but like Hans said, without a date modified cell or some other identifier that says “HEY! I’ve been changed!”, there is definitely going to be some manual checking…if only one table has been changed by one source, and another table has been changed by another source, then an update query would answer your problems….if everyone’s been muddling in everything, then everyone *might* be a little upset when you try to return to one database.

Reply | Quote