替换女士的访问问题[英] Ms Access replace issue

本文是小编为大家收集整理的关于替换女士的访问问题的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

所以,我必须更新一个包含 6000 行重复项的数据库.重复的公司都共享一个共同的数字代码.我的问题是我一次只替换一两个字段,即公司网站在 2009 年提供但他们在 2010 年没有提供.这就是所有需要替换的地方.我是一个入门新手,所有台词都是手工完成的,这让我想到了暴力.我只需要知道是否有可能让这个过程自动化.我知道我可以为此编写代码,如果 2010 值为 null,则需要替换它.任何帮助都会很棒.如果您需要有关该问题的更多信息,我也会在附近.

推荐答案

在评论中,您提供了这个示例数据(我已经创建了字段名称):

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

既然你说公司名称匹配,像这样:

  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;

这假设您要使用 2009 年记录的数据更新 2010 年的记录,并且每个公司每年只有一条记录.

要更新联系人姓名,您需要再次运行查询,适当地更改 SET 语句和 WHERE 子句:

  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;

如果有可能包含有用数据的较旧记录,则可以编写一些带有子查询的复杂 SQL 来一次性完成所有操作,但我只会使用相同 SQL 的多次运行来完成,首先比较 2010到 2009 年,然后到 2008 年,再到 2007 年,等等.最终,将不再有 Null 记录.

(这似乎更像是 StackOverflow 问题而不是 ServerFault 问题,但我知道什么?我永远无法弄清楚什么属于哪里)

本文地址:https://www.itbaoku.cn/post/597333.html

问题描述

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.

推荐答案

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)