I have about 40,000 rows and I am trying to update any blank cells under the heading <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Claim_Status</code>in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">_Data table</code> to <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">"UNKNOWN"</code> or output <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Claim_Status</code> in the table <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">"Status"</code>. 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.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">IIf(IsNull([_DATA]![Claim_Status]),"UNKNOWN",[Status]![Claim_Status])
</code>For example,
I have a row that has a null(empty)cell under the heading 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
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">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 LEFTJOIN Disease ON[_DATA].Disease_Category = Disease.Data_Disease_Type)LEFTJOIN[Plaintiff Firm]ON[_DATA].Plaintiff_Law_Firm =[Plaintiff Firm].Data_Firm)LEFTJOIN State ON[_DATA].State_Filed = State.Data_State)LEFTJOIN Status ON[_DATA].Claim_Status = Status.Data_Status_Type
WHERE(((Status.Claim_Status)=IIf(IsNull([_DATA]![Claim_Status]),"UNKNOWN",[Status]![Claim_Status])));</code>
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">IIf(IsNull([_DATA]![Claim_Status]),"UNKNOWN",[Status]![Claim_Status])
</code>For example,
I have a row that has a null(empty)cell under the heading 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
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">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 LEFTJOIN Disease ON[_DATA].Disease_Category = Disease.Data_Disease_Type)LEFTJOIN[Plaintiff Firm]ON[_DATA].Plaintiff_Law_Firm =[Plaintiff Firm].Data_Firm)LEFTJOIN State ON[_DATA].State_Filed = State.Data_State)LEFTJOIN Status ON[_DATA].Claim_Status = Status.Data_Status_Type
WHERE(((Status.Claim_Status)=IIf(IsNull([_DATA]![Claim_Status]),"UNKNOWN",[Status]![Claim_Status])));</code>
