Updating table

[Anandjhb]

Hi All

I need help with the following. I have a database in Access that has more than 150000 records. There is NO Primary key and over the past year or so people have been working with it and making some changes to the records. However our Head office has sent us a NEW version of the database. I need to be able to take the new verson and add it to the my existing database so that it can update it with records that have been removed and also update existing records with more current information. Note BOTH databases are the same in terms of having the SAME fields, structure etc except the NEW verson has changes and updates with needs to pulled into and my existing database. PLEASE tell me how I go about doing this, we are currently using Access 2000.

[Kevin Wilcox]

If the new data supercedes all of the old data you could just import the tables from the new db into the old, delete the old tables and rename the imported tables to the respective table names used in the old db. That's a quick & dirty method.

Or you could import the new tables, delete all the data in each relevant old table and append the new data into each table using append queries.

If some of the data in the old db supercedes the new db, you'll need to find some way of identifying which records in the old db to leave untouched; do you have, for instance, a field that records the date/time the record was last updated?

[Anandjhb]

If the new data supercedes all of the old data you could just import the tables from the new db into the old, delete the old tables and rename the imported tables to the respective table names used in the old db. That's a quick & dirty method.

Or you could import the new tables, delete all the data in each relevant old table and append the new data into each table using append queries.

If some of the data in the old db supercedes the new db, you'll need to find some way of identifying which records in the old db to leave untouched; do you have, for instance, a field that records the date/time the record was last updated?

Hi Kevin

There are only 2 tables, the OLD table and a copy of that table with updates which I call the NEW table. You see our head office asked me for a copy of my database which is actually made up of only ONE table so I sent it to them. They added data to some of the fields as well as MORE records and sent it back to me they added things like ID nos, tel nos, comments and other things. While they were working with the copy of the database we on our side were still updating and changing our records as normal. Now they sent us the copy and want me to update ONLY the fields where there are changes. So from the 150000 or more records not all the fields need to be updated only certain ones, is there a Query or something I can run against both tables so that ONLY the necessary fields in the old table can be updated with the changes from the new Table, and Kevin the fields in the old table would be empty so its not as if it has to check for info or anything it simply has to using some sort of identifier I guess I need to some how link both tables using the ID No and say where the fields are empty in the old table bring the info from the new table and dump it into the empty fields in my old table. But my questions are how do I link these tables and how do I populate the empty fields in the old table with the data from the new table.

[Kevin Wilcox]

It's probably easiest if you begin by creating new fields in your old table to match the newly added fields in the new table, with the same attributes all round.

Then populate these fields for the existing records in the old table; need to be careful here to match records correctly as you don't have a (populated) primary key in the old table; set up select query showing both old and HQ tables, and link any fields where the content will be identical in both, so that you effectively establish unique records. Check that the results are unique by a) running a find duplicates query against the same query, and b) checking the recordcount on the select query result against the recxordcount on the old table. hopefully they'll match. Once you are confident that the select query contains only unique records, change it to an update query and set the criteria for each field to be updated to Is Null, and the 'update to' box to the relevant field from the hq table. don't put anything in the fields you want unchanged.

next, use an append query to append any records in the hq table that aren't in your old table. Do this by adding both tables in the window, linking their ID field, making the join type 'all records from hq table etc', and then in the fields section, put all the fields from hq table and ALSO the ID field from oldtable, and set the criteria for this field to Is Null. The result should be only those records not already in the old table.

I don't mean to be pompous, but having made mistakes when migrating data many many times, can I recommend that you test this out on a copy of the old db first, and only make it final once you're really sure you got what you wanted - I've made the mistake of updating/migrating data, thought it all looked fine through a cursory check and no errors reported when migrating, only to find a few days later that there had been some errors; by which time the data in the post-migration tables has been changed by users and it becomes a nightmare to recover the situation!


QUOTE=Anandjhb]Hi Kevin

There are only 2 tables, the OLD table and a copy of that table with updates which I call the NEW table. You see our head office asked me for a copy of my database which is actually made up of only ONE table so I sent it to them. They added data to some of the fields as well as MORE records and sent it back to me they added things like ID nos, tel nos, comments and other things. While they were working with the copy of the database we on our side were still updating and changing our records as normal. Now they sent us the copy and want me to update ONLY the fields where there are changes. So from the 150000 or more records not all the fields need to be updated only certain ones, is there a Query or something I can run against both tables so that ONLY the necessary fields in the old table can be updated with the changes from the new Table, and Kevin the fields in the old table would be empty so its not as if it has to check for info or anything it simply has to using some sort of identifier I guess I need to some how link both tables using the ID No and say where the fields are empty in the old table bring the info from the new table and dump it into the empty fields in my old table. But my questions are how do I link these tables and how do I populate the empty fields in the old table with the data from the new table.[/quote]

[Anandjhb]

Hi Kevin

Thanks for the reply, what you worte I am sure makes a lot of sense to everyone but me. I am just a stupid data capturer who is trying to do something VERY advanced here. I have a reading knowledge of queries and things but never ever used them. So you have to be patient with me and PLEASE put up with my obvious lack of knowledge. Please can you list in step by step format how I do the queries because I really got confused trying to do them because you mentioned a few of them and as I said I am a bit stupid.

So I have the 2 tables: My old table and the new I received, the structure and the fields in both of them are the SAME and I know there are NO duplicates be I checked

1. In the query design I join the ID No field? do I drag from the old table to the new table or vice versa? because I want the OLD table to be updated.
2. Do I populate fields from the old table or the new table in the field design grid at the bottom?
3. There are 42 fields in BOTH the tables and all 42 or a combination of them will need to be populated in my old table.
4. After I finish with the field design grid which query do I use then?
5. How do I tell the query to populate the EMPTY fields in the OLD table.

It's probably easiest if you begin by creating new fields in your old table to match the newly added fields in the new table, with the same attributes all round.

Then populate these fields for the existing records in the old table; need to be careful here to match records correctly as you don't have a (populated) primary key in the old table; set up select query showing both old and HQ tables, and link any fields where the content will be identical in both, so that you effectively establish unique records. Check that the results are unique by a) running a find duplicates query against the same query, and b) checking the recordcount on the select query result against the recxordcount on the old table. hopefully they'll match. Once you are confident that the select query contains only unique records, change it to an update query and set the criteria for each field to be updated to Is Null, and the 'update to' box to the relevant field from the hq table. don't put anything in the fields you want unchanged.

next, use an append query to append any records in the hq table that aren't in your old table. Do this by adding both tables in the window, linking their ID field, making the join type 'all records from hq table etc', and then in the fields section, put all the fields from hq table and ALSO the ID field from oldtable, and set the criteria for this field to Is Null. The result should be only those records not already in the old table.

I don't mean to be pompous, but having made mistakes when migrating data many many times, can I recommend that you test this out on a copy of the old db first, and only make it final once you're really sure you got what you wanted - I've made the mistake of updating/migrating data, thought it all looked fine through a cursory check and no errors reported when migrating, only to find a few days later that there had been some errors; by which time the data in the post-migration tables has been changed by users and it becomes a nightmare to recover the situation!


QUOTE=Anandjhb]Hi Kevin

There are only 2 tables, the OLD table and a copy of that table with updates which I call the NEW table. You see our head office asked me for a copy of my database which is actually made up of only ONE table so I sent it to them. They added data to some of the fields as well as MORE records and sent it back to me they added things like ID nos, tel nos, comments and other things. While they were working with the copy of the database we on our side were still updating and changing our records as normal. Now they sent us the copy and want me to update ONLY the fields where there are changes. So from the 150000 or more records not all the fields need to be updated only certain ones, is there a Query or something I can run against both tables so that ONLY the necessary fields in the old table can be updated with the changes from the new Table, and Kevin the fields in the old table would be empty so its not as if it has to check for info or anything it simply has to using some sort of identifier I guess I need to some how link both tables using the ID No and say where the fields are empty in the old table bring the info from the new table and dump it into the empty fields in my old table. But my questions are how do I link these tables and how do I populate the empty fields in the old table with the data from the new table.

[/quote]