MS存取表变换

Question

我有一个从这样的应用程序导出的txt文件：

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

我在MS中导入这个文件，并获得一个表(赋值)。

但我需要的是这个结果：

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

• 我必须使用
• 只有5‘颜色’使用。

有解决办法吗？非常感谢

Answer 1

使用这样的查询：

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;