0
UPDATE _DATA SET 
[_DATA].Claim_Status = "UNKNOWN", 
[_DATA].State_Filed = "XX", 
[_DATA].Disease_Category = "UNKNOWN"
WHERE 
((([_DATA].Claim_Status) Is Null) AND 
(([_DATA].State_Filed) Is Null) AND 
(([_DATA].Disease_Category) Is Null));

This code is what I got from typing in criteria in query design. It seems that this only runs when claim_status, state_filed, and disease_category are all null (empty) cells. I want to run a query that fills in "UNKNOWN" into empty cells under Claim_Status, "XX" int empty cells under State_Filed, etc. The condition should not include "AND" which means they are basically run separately.

Can anyone help me with this?

Thanks in advance.

Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46
BLkrn
  • 77
  • 2
  • 14
  • 1
    Consider 3 simple queries --- one for each of the fields you want to update. Here is the first of them: `UPDATE _DATA SET Claim_Status = "UNKNOWN" WHERE Claim_Status Is Null` – HansUp Jun 05 '15 at 19:22
  • Does that mean I have to have 3 queries to make this changes? – BLkrn Jun 05 '15 at 19:23

2 Answers2

1

This query will only update Claim_Status, State_Filed and Disease_Category if all three of those fields are NULL. Say, for instance, that the State_Filed is NULL, but Disease_Category and Claim_Status have data populated. In this case, your update would not update State_Filed because not all three columns are NULL. You should use three separate queries to make sure that all NULL columns get updated regardless of if the other two are populated:

UPDATE _DATA
SET Claim_Status = 'UNKNOWN' 
WHERE Claim_Status IS NULL

UPDATE _DATA
SET State_Filed = 'XX' 
WHERE State_Filed IS NULL

UPDATE _DATA
SET Disease_Category = 'UNKNOWN' 
WHERE Disease_Category IS NULL

Also, I would personally not update NULL fields to contain data. If you don't have data for these columns, you don't have the data. If, for instance, you want a NULL state to show as XX instead of NULL, the place to do this is in your presentation layer or reports that you write off this data, not the database itself.

Patrick Tucci
  • 1,824
  • 1
  • 16
  • 22
  • Thanks for your input. Is it possible to use that chunk of lines in one update query so that it does all at once? – BLkrn Jun 05 '15 at 19:40
  • I don't believe so, each `UPDATE` is a separate transaction and must be run by itself. See this for a lengthy workaround: http://stackoverflow.com/questions/1838593/how-do-i-execute-multiple-sql-statements-in-access-query-editor. Now you could potentially use a case statement to update the value to your default if it is `NULL` or to set it back to itself if it is not, but that would touch every single column of every single row in the DB. I really would not recommend that. – Patrick Tucci Jun 05 '15 at 19:47
1

You can use Nz:

UPDATE 
    _DATA 
SET 
    Claim_Status = Nz(Claim_Status, "UNKNOWN"), 
    State_Filed = Nz(State_Filed, "XX"), 
    Disease_Category = Nz(Disease_Category, "UNKNOWN")
WHERE 
    Claim_Status Is Null 
    OR 
    State_Filed Is Null 
    OR 
    Disease_Category Is Null;
Gustav
  • 53,498
  • 7
  • 29
  • 55