Microsoft Access压缩表中的多行

Question

我在MS Access 2007中有一个问题，我希望有人有答案。我有一个很长但很简单的表，其中包含客户名称以及交付周的日期。我想通过将名称和所有日期列入一个新字段“ALLDays”，同时仍然保留所有数据来总结此表。

源表看起来像这样：

Name         Day  

CustomerA    Monday  

CustomerA    Thursday  

CustomerB    Tuesday  

CustomerB    Friday  

CustomerC    Wednesday  

CustomerC    Saturday  

我想有一个返回如下结果的查询：

Name         ALLDays  

CustomerA    Monday, Thursday  

CustomerB    Tuesday, Friday  

CustomerC    Wednesday, Saturday  

谢谢。

Answer 1

由于这只是一小部分选项，另一种没有VBA的方法是设置一系列IIF语句并连接结果。SELECT name,    IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") &    IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") &    IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") &    IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, ") AS AllDaysFROM Table1GROUP BY name如果你是一个完美主义者，你甚至可以摆脱这样的最后一个逗号SELECT name, LEFT(   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") &    IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") &    IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") &    IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, "),LEN(   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") &    IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") &    IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") &    IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, ")) - 2)AS AllDaysFROM Table1GROUP BY name您还可以考虑将它们保存在单独的列中，因为如果从另一个列访问此查询，这可能会更有用。例如，通过这种方式查找只有星期二的实例会更容易。就像是：SELECT name, IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday") AS Monday,  IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday") AS Tuesday,IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday") AS Wednesday,IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday") AS Thursday,IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday") AS Friday,IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday") AS Saturday,IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday") AS SundayFROM Table1GROUP BY name