Ms Access replace issue.

Question

So, I have to update a database with 6000 lines of duplicates. The duplicates are companies who all share a common number code. My issue is I'm only replacing one or two fields at a time, ie company web site that was provided in 2009 but they didn't give it in 2010. This is all that needs to be replaced. I'm an access rookie and am doing all lines by hand, its making me contemplate violence. I just need to know if its possible to have this process automated. I know i could write code for this, if the 2010 value is null it needs to be replaced. Any help would be great. I'll also be around if you need some more info on the problem.

Answer 1

Can you give us an idea of what the table(s) looks like? What fields are there? What do you mean by duplicates?

Answer 2

In a comment, you supplied this sample data (I've created field names):

  CompanyName CompanyYear  Amount   Website         Contact
  starbucks   2009         100$     starbucks.com   matt bucky
  starbucks   2010         200$     null            null

Since you've said the company names match, something like this:

  UPDATE tblCompany INNER JOIN tblCompany AS C2 ON tblCompany.CompanyName = C2.CompanyName
  SET tblCompany.Website = C2.Website
  WHERE tblCompany.Website Is Null AND C2.Website Is Not Null
     AND tblCompany.CompanyYear=2010 AND C2.CompanyYear=2009;

This assumes that you want to update 2010 records with data from 2009 records, and that for each company there's only one record for each year.

To update the contact name, you'd run the query again, suitably altering the SET statement and WHERE clause:

  UPDATE tblCompany INNER JOIN tblCompany AS C2 ON tblCompany.CompanyName = C2.CompanyName
  SET tblCompany.Contact = C2.Contact
  WHERE tblCompany.Contact Is Null AND C2.Contact Is Not Null
     AND tblCompany.CompanyYear=2010 AND C2.CompanyYear=2009;

If there are older records that might have useful data, it's possible to write some complicated SQL with subqueries that would do it all in one go, but I'd just do it with multiple runs of the same SQL, first comparing 2010 to 2009, then to 2008, then to 2007, etc. Eventually, there won't be any more Null records.

(this also seems more like a StackOverflow question than a ServerFault one, but what do I know? I can never figure out what belongs where)