和/或在访问和计数多个字段中

Question

我正试图从下表中得到2015年前的晋升总数。

​

我使用了以下代码

SELECT COUNT(*) AS [Total Promotions Before 2015] FROM [Employment History] 
WHERE IIF([Date of 1st Promotion]<#1/1/2015#,1,0)
OR  IIF([Date of 2nd Promotion]<#1/1/2015#,1,0)
OR  IIF([Date of 3rd Promotion]<#1/1/2015#,1,0)
OR  IIF([Date of 4th Promotion]<#1/1/2015#,1,0)
OR  IIF([Date of 5th Promotion]<#1/1/2015#,1,0)
;

然而，这只是给出了2015年前有晋升的人数，而没有给出晋升人数。有没有办法让这个更像+1，如果在第一年升职，+1，如果在第二年，等等

Answer 1

这是你想要的吗？

SELECT COUNT(*) AS [Total Promotions Before 2015]
FROM [Employment History] 
WHERE [Date of 1st Promotion] < #1/1/2015# OR
      [Date of 2nd Promotion] < #1/1/2015# OR
      [Date of 3rd Promotion] < #1/1/2015# OR
      [Date of 4th Promotion] < #1/1/2015# OR
      [Date of 5th Promotion] < #1/1/2015#;

但我觉得你真的想：

SELECT SUM(IIF([Date of 1st Promotion] < #1/1/2015#, 1, 0)) +
       SUM(IIF([Date of 2nd Promotion] < #1/1/2015#, 1, 0)) +
       SUM(IIF([Date of 3rd Promotion] < #1/1/2015#, 1, 0)) +
       SUM(IIF([Date of 4th Promotion] < #1/1/2015#, 1, 0)) +
       SUM(IIF([Date of 5th Promotion] < #1/1/2015#, 1, 0))
FROM [Employment History] ;