在 MS ACCESS 中的同一個表上唯一地連接列

原表：

=======================================================================================
|   A   |     B    |       C        |     D     |     E     |      F      |     G     |
=======================================================================================
| 65432  | AFD452E |   sometext1    |   IA1     |   10,6    |    8,5      |   22,0    |
| 65432  | AFD452E |   sometext1    |   IA2     |           |    11,4     |   16,5    |
| 65432  | AFD452E |   sometext1    |   IA3     |           |    3,5      |    5,3    |
| 65989  | 74652E  |   sometext2    |   IA1     |   3,3     |             |     8     |
| 65989  | 74652E  |   sometext2    |   IA3     |   23      |     1,1     |    6,9    |
| 27890  | P8965A  |   sometext3    |   IA3     |   12,7    |     13,8    |   33,2    |
| 34432  | P673452 |   sometext6    |   IA6     |           |             |           |
| 55770  | 27799A  |   sometext7    |   IA7     |           |             |           |
=======================================================================================

結果表應為：

=======================================================================================
|   A   |     B    |       C        |     NEWA1     |     NEWA2     |      NEWA3      |
=======================================================================================
| 65432  | AFD452E |   sometext1    | 10,6-8,5-22,0-| -11,4-16,5-   |                 |
| 65989  | 74652E  |   sometext2    | 3,3--8-       |               |  23-1,1-6,9-    |
| 27890  | P8965A  |   sometext3    |               |               | 12,7-13,8-33,2- |
=======================================================================================

這是我到目前為止得到的：

SELECT A, B, C,
max(iif([D] like "IA1" .. rest of code)) as newa1,
max(iif([D] like "IA2" .. rest of code)) as newa2,
max(iif([D] like "IA3" .. rest of code)) as newa3
from table
group by A, B,C;

這是該查詢的輸出：

=======================================================================================
|   A   |     B    |       C        |     NEWA1     |     NEWA2     |      NEWA3      |
=======================================================================================
| 65432  | AFD452E |   sometext1    | 10,6-8,5-22,0-| -11,4-16,5-   |                 |
| 65989  | 74652E  |   sometext2    | 3,3--8-       |               |  23-1,1-6,9-    |
| 27890  | P8965A  |   sometext3    |               |               | 12,7-13,8-33,2- |
| 34432  | P673452 |   sometext6    |               |               |                 |
| 55770  | 27799A  |   sometext7    |               |               |                 |
=======================================================================================

看起來您只需要在查詢中添加 WHERE 過濾器：

SELECT
 A, B, C, ...
FROM
 YourTable
**WHERE
D IN ('IA1', 'IA2', 'IA3')**
GROUP BY
 A, B, C
;

這樣，那些A, B, C沒有'IA1', 'IA2'or 'IA3'in 列的組D將從輸出中排除。

引用自：https://dba.stackexchange.com/questions/107724