我在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 謝謝。
3 回答
小怪獸愛吃肉
TA貢獻1852條經驗 獲得超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 AllDays
FROM Table1
GROUP 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 AllDays
FROM Table1
GROUP 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 Sunday
FROM Table1
GROUP BY name
- 3 回答
- 0 關注
- 743 瀏覽
添加回答
舉報
0/150
提交
取消