删除多余的访问字段单元[英] Remove redundant Access field cells

问题描述

```SELECT
IIf(Name_2010 In (Name_2011, Name_2012), '', Name_2010)
AS N1,
IIf(Name_2011 In (Name_2010, Name_2012), '', Name_2011)
AS N2,
IIf(Name_2012 In (Name_2010, Name_2011), '', Name_2012)
AS N3
FROM Table1;
```

推荐答案

```SELECT ID, N1 &
IIf(N2 <> N1, N2, '') &
IIf((N3 <> N2) And (N3 <> N1), N3, '') AS Name_Final
FROM
(SELECT ID, Nz(Name_2010) AS N1, Nz(Name_2011) AS N2, Nz(Name_2012) AS N3
FROM Table1) AS T
ORDER BY ID;
```

ORDER BY 子句是为了它所说的，而不是作为"Name_Final"计算的一部分.

问题描述

Continued from Combine Access fields into one field given two queries

I have the table below with three main fields Name_2010, Name_2011 and Name_2012 and they need to be integrated as Name_Final.

I used the query below to select only a particular member of the three fields per row but currently it does not work as intended as it does not recognize redundant cells.

```SELECT
IIf(Name_2010 In (Name_2011, Name_2012), '', Name_2010)
AS N1,
IIf(Name_2011 In (Name_2010, Name_2012), '', Name_2011)
AS N2,
IIf(Name_2012 In (Name_2010, Name_2011), '', Name_2012)
AS N3
FROM Table1;
```

What query should I use to achieve Name_Final given my current table?

推荐答案

```SELECT ID, N1 &
IIf(N2 <> N1, N2, '') &
IIf((N3 <> N2) And (N3 <> N1), N3, '') AS Name_Final
FROM
(SELECT ID, Nz(Name_2010) AS N1, Nz(Name_2011) AS N2, Nz(Name_2012) AS N3
FROM Table1) AS T
ORDER BY ID;
```

The ORDER BY clause is for what it says, rather than being a part of the 'Name_Final' calculation.