访问 - 报表格式和显示

Question

我有一个表格，日期如下 3/2/2014、3/5/2014、6/21/2014、8/16/2014、1/5/2015。我需要一种方法来显示每个月的每一天，如果日期存在，则显示 Y，如果不存在，则显示 N。该表是动态的，可以向其添加更多日期。

例：

          March 2014
1 | 2 | 3 | 4 | 5 | ... | 31
N | N | Y | N | Y | ... | N
          July 2014
1 | 2 | ... | 20 | 21 | 22 | ... | 31
N | N | ... | N  | Y  | N  | ... | N
          August 2014
1 | 2 | ... | 20 | 21 | 22 | ... | 31
N | N | ... | N  | Y  | N  | ... | N
          January 2015
1 | 2 | ... | 15 | 16 | 17 | ... | 31
N | N | ... | N  | Y  | N  | ... | N

我是 Access 的新手，因此任何帮助将不胜感激:)

更新：如果我想将上面的示例更改为我现在想要返回项目 ID 而不是只返回"Y"或"N"的位置，我将如何编辑 IIF 语句？项目 ID 与日期存储在同一表中。

          March 2014
1 | 2 |  3  | 4  | 5   | ... | 31
N | N |  4B | N  | 2W  | ... | N
          July 2014
1 | 2 | ... | 20 | 21  | 22  | ... | 31
N | N | ... | N  | Z8  | N   | ... | N
          August 2014
1 | 2 | ... | 20 | 21  | 22  | ... | 31
N | N | ... | N  | E5  | N   | ... | N
          January 2015
1 | 2 | ... | 15 | 16  | 17  | ... | 31
N | N | ... | N  | 1FF | N   | ... | N

Answer 1

您需要编写交叉表查询（透视）。像这样：

TRANSFORM iif(Count(*)>0,"Y","N") AS Val
SELECT Year(YourDate) AS Y, Month(YourDate) AS M
FROM YourTable
GROUP BY Year(YourDate), Month(YourDate)
PIVOT Day(YourDate) In ("01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31");