结合接入领域到一个场给出两个查询 [英] Combine Access fields into one field given two queries
问题描述
我的 N 的MS接入领域,需要每行检查一个法律单元。一个合法的电池根本没有价值失踪,未知;或不为空。所有这些细胞将被合并成一个单一的字段仅包含细胞与合法值
参考下面的表格,
Name_Final
将包含从 Name_2010
, Name_2011 $ C $这些法律的细胞C>和
Name_2012
。
我已经有两个单独的查询,帮我做的工作,但我需要他们的结果结合起来,为了让我得到了 Name_Final
字段。
-
SELECT NZ(Name_2010,'')及 NZ(Name_2011,'')及 NZ(Name_2012,'') AS Name_Final
和的结果是:
-
过滤器细胞不需要值
SELECT Name_2010不是在('失踪','未知','空白(S)','',NZ) Name_2011不是在('失踪','未知','空白(S)','',NZ) Name_2012不是在('失踪','未知','空白(S)','',NZ) AS Name_Final 从表1;
这其中一个返回 0
, 1
或空白在未标明值在
括号,在没有指示的值。在
parenthesis`或空白值分别。
它的输出是:
我需要找到一种方法,这两套查询的融合到一起,拿出 Name_Final
。请帮助,使宿主。
SELECT IIF(Name_2010在(NULL,'失踪','未知','空白(S), '),'',Name_2010)及
IIF(Name_2011在(NULL,'失踪','未知','空白(S)',''),'',Name_2011)及
IIF(Name_2012在(NULL,'失踪','未知','空白(S)',''),'',Name_2012)
AS Name_Final
从表1;
这是说,我会倾向于刚刚清理数据,并更换所有'失踪','未知'或'空白(S)与NULL值,这将允许简单的新西兰的更换IIF的 -
UPDATE表1 SET Name_2010 = NULL WHERE修剪(Name_2010)在('失踪','未知','空白(S)','');
UPDATE表1 SET Name_2011 = NULL WHERE修剪(Name_2011)在('失踪','未知','空白(S)','');
UPDATE表1 SET Name_2012 = NULL WHERE修剪(Name_2012)在('失踪','未知','空白(S)','');
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,
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.
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:
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:
I need to find a way to integrate these two sets of queries together to come up with Name_Final
. Please help SO Overlords.
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)', '');
这篇关于结合接入领域到一个场给出两个查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!