CODEWITHSUNDEEP
ms-access
sunlight76
sunlight76

Reputation: 103

MS Access Table transformation

I have a txt file exported from an application like this:

+---------+----------+-------+-------+-------+-------+-------+
| Month   | Name     | Day1  | Day2  | Day3  | [...] | Day31 |
+---------+----------+-------+-------+-------+-------+-------+
| January | Mr Red   | White | Green |       |       | White |
| January | Mr Grey  | Red   | White | White |       | Red   |
| January | Mr White | Green |       | White | White | Red   |
+---------+----------+-------+-------+-------+-------+-------+

I import this file in MS-Access and I obtain a table (Assignments).

But what I need is this result:

+---------+----------+-------+-----------+
| Month   | Name     | Color | Occurency |
+---------+----------+-------+-----------+
| January | Mr Red   | White | 2         |
| January | Mr Red   | Green | 1         |
| January | Mr Grey  | Red   | 2         |
| January | Mr Grey  | White | 2         |
| January | Mr White | Green | 1         |
| January | Mr White | White | 2         |
| January | Mr White | Red   | 1         |
+---------+----------+-------+-----------+

Is there a solution? Many thanks

Upvotes: 0

Views: 26

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

Use query like this:

SELECT [Month], [Name], Color, COUNT(Color) as Occurency FROM (
    SELECT [Month], [Name], Day1 as Color FROM Assignments
    UNION ALL
    SELECT [Month], [Name], Day2 as Color FROM Assignments
    UNION ALL
    SELECT [Month], [Name], Day3 as Color FROM Assignments
    UNION ALL
    ....
    SELECT [Month], [Name], Day31 as Color FROM Assignments)
WHERE Color is not null
GROUP BY [Month], [Name], Color;

Upvotes: 1

Related Questions