Search code examples
sqldatabasems-accessvbams-access-2010

Combine Access fields into one field given two queries

I have n MS Access fields that needs checking per row for one legal cell. A legal cell simply does not have the values "missing", "unknown"; or is not blank. All of these cells will be combined into a single field that only contains cell with legal values.

Referring to the table below,

coven

Name_Final will contain these legal cells from Name_2010, Name_2011 and Name_2012.

I already have two separate queries to help me do the job but I need to combine their results in order for me to get the Name_Final field.

  1. Query that returns non-null or non-empty cells

     SELECT 
     Nz(Name_2010, '') & 
     Nz(Name_2011, '') & 
     Nz(Name_2012, '') 
     AS Name_Final

and the result is:

coven-query-1

  1. Filter cells with undesired values

     SELECT 
     Name_2010 Not In ('missing', 'unknown', 'Blank(s)', ' ', Nz), 
     Name_2011 Not In ('missing', 'unknown', 'Blank(s)', ' ', Nz), 
     Name_2012 Not In ('missing', 'unknown', 'Blank(s)', ' ', Nz) 
     AS Name_Final
     FROM Table1;

This one returns 0, -1 or blank for values indicated in the NOT IN parenthesis, values not indicated in the NOT IN parenthesis` or for blank values respectively.

Its output is:

coven-query-2

I need to find a way to integrate these two sets of queries together to come up with Name_Final.

Solution 
  • SELECT IIf(Name_2010 In (Null, 'missing', 'unknown', 'Blank(s)', ' '), '', Name_2010) &
   IIf(Name_2011 In (Null, 'missing', 'unknown', 'Blank(s)', ' '), '', Name_2011) &
   IIf(Name_2012 In (Null, 'missing', 'unknown', 'Blank(s)', ' '), '', Name_2012) 
   AS Name_Final
FROM Table1;

    That said, I would be inclined to just clean up the data and replace all 'missing', 'unknown' or 'Blanks(s)' values with NULL, which would allow replacing the IIf's with simple Nz's -

    UPDATE Table1 SET Name_2010 = Null WHERE Trim(Name_2010) In ('missing', 'unknown', 'Blank(s)', '');
UPDATE Table1 SET Name_2011 = Null WHERE Trim(Name_2011) In ('missing', 'unknown', 'Blank(s)', '');
UPDATE Table1 SET Name_2012 = Null WHERE Trim(Name_2012) In ('missing', 'unknown', 'Blank(s)', '');