我有大约40,000行，我试图更新任何空白单元格 标题 Claim_Status 在 _Data表根据未知或输出 Claim_Status 表中的状态。当我运行选择查询，行数大幅缩减至20000。问题是，我需要所有的40000行。

  IIF（ISNULL（[_数据]！[Claim_Status]），未知，[状态]！[Claim_Status]）
 

例如，

我有一个排，有在_Data表ehading Claim_Status下一个空（空）单元。当我运行一个查询，我还是应该包括此行与未知添加到Claim_Status，而不是一个空单元格。

然而，当我运行查询，该行消失，行的总数下降至约20,000。我猜测，已经消失的行也有类似的问题。

我运行一个选择查询，而不是更新查询您的信息。

此外，对于Claim_Status输出不是从_Data表，而是，链接到它的一个值。在_Data表Claim_Status列链接到Data_Status_Type在一个不同的表称为状态，其输出是任何对应于从Data_Status_Type值

这是什么原因造成的全部麻烦？

编辑）

SQL code

  SELECT [_DATA] .Claimant_Name，[_DATA] .Account_ID，[_DATA] .Claim_ID，[_DATA] .Account_Name，[_DATA] .Claim_Type，[_DATA] .Coverage，[ _DATA] .Claim_Level，[_DATA] .Claim_Count，[_DATA] .File_Date，[_DATA] .File_Year，[_DATA] .Resolution_Date，[_DATA] .Resolution_Year，Status.Claim_Status，[_DATA] .Indemnity_Paid，Disease.Disease_Category，国家.State_Filed，[_DATA] .First_Exposure_Date，[_DATA] .Last_Exposure_Date，[_DATA] .Claimant_Employee，[_DATA] .Claimant_DOB，[_DATA] .Claimant_Deceased，[_DATA] .Claimant_DOD，[_DATA] .Claimant_Diagnosis_Date，[_DATA] .Product_Type [_DATA] .Product_Line，[_DATA] [公司/实体/ PC]，[原告公司] .Plaintiff_Law_Firm，[_DATA] .Asbestos_Type，[_DATA] .Evaluation_Date，[_DATA] .Tier，[_DATA] .Data_Source， [_DATA] .Data_Source_Category，[_DATA]。[辖区/县]，[_DATA] .Settlement_Demand，[_DATA] .Jury_Verdict，[_DATA] .Exposure_Site，[_DATA] .National_Defendant_Firm，[_DATA] .Local_Defendant_Firm，[_DATA]。 Expense_Amount，[_DATA] .NCC_Expense_Amount，[_DATA] .Non_NCC_Expense_Amount
从（（（_DATA LEFT JOIN疾病ON [_DATA] .Disease_Category = Disease.Data_Disease_Type）LEFT JOIN [原告公司] ON [_DATA] .Plaintiff_Law_Firm = [原告公司] .Data_Firm）LEFT JOIN国开[_DATA] .State_Filed =国家.Data_State）LEFT JOIN状态ON [_DATA] .Claim_Status = Status.Data_Status_Type
WHERE（（（Status.Claim_Status）= IIF（ISNULL（[_数据] [Claim_Status]），未知，[状态] [Claim_Status]））！）;
 

解决方案

如果我理解正确，你追求的是什么，这是从最左边的表中返回的所有行 [_ DATA] ，并简单地替换空值在 [_数据]！[Claim_Status] 与未知，那么你就应该放弃了，其中子句和字段添加到新西兰函数中的 SELECT 语句（的 http://www.techonthenet.com/access/functions/advanced/nz.php ）。下面是这个例子的SQL：

  SELECT NZ（[_数据]！[Claim_Status]，未知）为[_DATA_Claim_Status]，[_DATA] .Claimant_Name，[_DATA] .Account_ID，[_DATA] .Claim_ID， [_DATA] .Account_Name，[_DATA] .Claim_Type，[_DATA] .Coverage，[_DATA] .Claim_Level，[_DATA] .Claim_Count，[_DATA] .File_Date，[_DATA] .File_Year，[_DATA] .Resolution_Date，[_DATA ] .Resolution_Year，Status.Claim_Status，[_DATA] .Indemnity_Paid，Disease.Disease_Category，State.State_Filed，[_DATA] .First_Exposure_Date，[_DATA] .Last_Exposure_Date，[_DATA] .Claimant_Employee，[_DATA] .Claimant_DOB，[_DATA]。 Claimant_Deceased，[_DATA] .Claimant_DOD，[_DATA] .Claimant_Diagnosis_Date，[_DATA] .Product_Type，[_DATA] .Product_Line，[_DATA] [公司/实体/ PC]，[原告公司] .Plaintiff_Law_Firm，[_DATA] .Asbestos_Type [_DATA] .Evaluation_Date，[_DATA] .Tier，[_DATA] .Data_Source，[_DATA] .Data_Source_Category，[_DATA]。[辖区/县]，[_DATA] .Settlement_Demand，[_DATA] .Jury_Verdict，[_DATA] .Exposure_Site，[_DATA] .National_Defendant_Firm，[_DATA] .Local_Defendant_Firm，[_DATA] .Expense_Amount，[_DATA] .NCC_Expense_Amount，[_DATA] .Non_NCC_Expense_Amount
 从（（（_DATA LEFT JOIN疾病ON [_DATA] .Disease_Category = Disease.Data_Disease_Type）LEFT JOIN [原告公司] ON [_DATA] .Plaintiff_Law_Firm = [原告公司] .Data_Firm）LEFT JOIN国开[_DATA] .State_Filed =国家.Data_State）LEFT JOIN状态ON [_DATA] .Claim_Status = Status.Data_Status_Type;
 

这是，其中子句不具有所期望的效果，因为它说的是，如果 [_数据]！[Claim_Status] 为null，则返回行，其中 Status.Claim_Status =未知，否则返回行，其中 Status.Claim_Status 等于本身。而且，因为你包括你的，其中第一个外连接表你有效地将那到 INNER JOIN 。

I have about 40,000 rows and I am trying to update any blank cells under the heading Claim_Status in _Data table to "UNKNOWN" or output Claim_Status in the table "Status". When I run the selection query, the number of rows shrink drastically to 20,000. The problem is that I need all 40,000 rows.

IIf(IsNull([_DATA]![Claim_Status]),"UNKNOWN",[Status]![Claim_Status])

For example,

I have a row that has a null(empty)cell under the ehading Claim_Status in the _Data table. When I run a query, I should still be including this row with "UNKNOWN" added to "Claim_Status" instead of an empty cell.

However, when I run the query, the row disappears and the total number of rows goes down to about 20,000. I am guessing that the rows that have disappeared have similar problems.

I am running a 'selection' query not 'update' query for your information.

Also, the output for Claim_Status is NOT the value from _Data table but instead, the one that is linked to it. Claim_Status column in _Data table is linked to Data_Status_Type in a different table called "Status" and the output is whatever that corresponds to the value from Data_Status_Type.

Is this what is causing the whole trouble?

Edited)

SQL code

   SELECT [_DATA].Claimant_Name, [_DATA].Account_ID, [_DATA].Claim_ID, [_DATA].Account_Name, [_DATA].Claim_Type, [_DATA].Coverage, [_DATA].Claim_Level, [_DATA].Claim_Count, [_DATA].File_Date, [_DATA].File_Year, [_DATA].Resolution_Date, [_DATA].Resolution_Year, Status.Claim_Status, [_DATA].Indemnity_Paid, Disease.Disease_Category, State.State_Filed, [_DATA].First_Exposure_Date, [_DATA].Last_Exposure_Date, [_DATA].Claimant_Employee, [_DATA].Claimant_DOB, [_DATA].Claimant_Deceased, [_DATA].Claimant_DOD, [_DATA].Claimant_Diagnosis_Date, [_DATA].Product_Type, [_DATA].Product_Line, [_DATA].[Company/Entity/PC], [Plaintiff Firm].Plaintiff_Law_Firm, [_DATA].Asbestos_Type, [_DATA].Evaluation_Date, [_DATA].Tier, [_DATA].Data_Source, [_DATA].Data_Source_Category, [_DATA].[Jurisdiction/County], [_DATA].Settlement_Demand, [_DATA].Jury_Verdict, [_DATA].Exposure_Site, [_DATA].National_Defendant_Firm, [_DATA].Local_Defendant_Firm, [_DATA].Expense_Amount, [_DATA].NCC_Expense_Amount, [_DATA].Non_NCC_Expense_Amount
FROM (((_DATA LEFT JOIN Disease ON [_DATA].Disease_Category = Disease.Data_Disease_Type) LEFT JOIN [Plaintiff Firm] ON [_DATA].Plaintiff_Law_Firm = [Plaintiff Firm].Data_Firm) LEFT JOIN State ON [_DATA].State_Filed = State.Data_State) LEFT JOIN Status ON [_DATA].Claim_Status = Status.Data_Status_Type
WHERE (((Status.Claim_Status)=IIf(IsNull([_DATA]![Claim_Status]),"UNKNOWN",[Status]![Claim_Status])));

If I correctly understand what you're after, which is to return all rows from the left-most table [_DATA], and simply replace NULLs in [_DATA]![Claim_Status] with "UNKNOWN", then you should drop the WHERE clause, and add the field to your SELECT statement within the Nz function (http://www.techonthenet.com/access/functions/advanced/nz.php). Here is the example SQL:

 SELECT Nz([_DATA]![Claim_Status], "UNKNOWN") As [_DATA_Claim_Status], [_DATA].Claimant_Name, [_DATA].Account_ID, [_DATA].Claim_ID, [_DATA].Account_Name, [_DATA].Claim_Type, [_DATA].Coverage, [_DATA].Claim_Level, [_DATA].Claim_Count, [_DATA].File_Date, [_DATA].File_Year, [_DATA].Resolution_Date, [_DATA].Resolution_Year, Status.Claim_Status, [_DATA].Indemnity_Paid, Disease.Disease_Category, State.State_Filed, [_DATA].First_Exposure_Date, [_DATA].Last_Exposure_Date, [_DATA].Claimant_Employee, [_DATA].Claimant_DOB, [_DATA].Claimant_Deceased, [_DATA].Claimant_DOD, [_DATA].Claimant_Diagnosis_Date, [_DATA].Product_Type, [_DATA].Product_Line, [_DATA].[Company/Entity/PC], [Plaintiff Firm].Plaintiff_Law_Firm, [_DATA].Asbestos_Type, [_DATA].Evaluation_Date, [_DATA].Tier, [_DATA].Data_Source, [_DATA].Data_Source_Category, [_DATA].[Jurisdiction/County], [_DATA].Settlement_Demand, [_DATA].Jury_Verdict, [_DATA].Exposure_Site, [_DATA].National_Defendant_Firm, [_DATA].Local_Defendant_Firm, [_DATA].Expense_Amount, [_DATA].NCC_Expense_Amount, [_DATA].Non_NCC_Expense_Amount
 FROM (((_DATA LEFT JOIN Disease ON [_DATA].Disease_Category = Disease.Data_Disease_Type) LEFT JOIN [Plaintiff Firm] ON [_DATA].Plaintiff_Law_Firm = [Plaintiff Firm].Data_Firm) LEFT JOIN State ON [_DATA].State_Filed = State.Data_State) LEFT JOIN Status ON [_DATA].Claim_Status = Status.Data_Status_Type;

That WHERE clause isn't having the desired effect because what it says is if [_DATA]![Claim_Status] Is Null then return rows where Status.Claim_Status = "UNKNOWN", otherwise return rows where Status.Claim_Status equals itself. And, because you're including an outer joined table in your WHERE clause you're effectively turning that into an INNER JOIN.