MS Access – Concatenate Columns Uniquely on the Same Table

ms accesspivot

The original table:

=======================================================================================
|   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     |           |             |           |
=======================================================================================

The resulting table should be:

=======================================================================================
|   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- |
=======================================================================================

This is what I got so far :

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;

This is the output of that query :

=======================================================================================
|   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    |               |               |                 |
=======================================================================================

Best Answer

Looks like you just need to add a WHERE filter to your query:

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

That way those A, B, C groups that do not have 'IA1', 'IA2' or 'IA3' in column D will be excluded from the output.

Related Question