MS ACCESS 交叉表格数据

Question

MS ACCESS 交叉表格数据

3

我有两个表格，一个包含员工详细信息，如下所示：

Employee ID | Employee Name | Start Date | Termination Date |

2234        |  John Smith  | 2014-01-03  |                  |   

3333        | Jane Doe     | 2014-04-22  |  2014-10-31      |

1234        | Bobby Wilson | 2013-12-10  |                  |

我的第二个表格按月份-年份列出了销售额

看起来像这样

Employee ID | 2013-12 |   2014-01   | 2014-02  | 2014-03  |  2014-04 | etc | etc 

2234        |         |   199.99    |  130.00  |  300.00  |  230.99  | etc | etc

3333        |         |             |          |          |  204.02  | etc | etc

1234        |  455.99 |   332.32    |  334.00  |  553.00  |  334.99  | etc | etc

我需要的是制作一个新的查询，帮助我展示员工在他们入职的第一个月、第二个月、第三个月等销售数字的趋势，以入职的月份为基准。时间跨度为12个月或更长时间。

那么新表格应该如下所示：

Employee    |  Month 1  |  Month 2  |  Month 3  | Month 4  | etc |  etc |  

2234        |   199.99  |  130.00   |  300.00   |  230.99  | etc |  etc |

3333        |  240.02  |  (month 2) |  (month 3)| (month 4)| etc |  etc |       

1234        |  455.99  |  332.32    |   334.00  |  553.00  | etc |  etc |

我知道我们可以通过员工ID将两个表连接起来，我也知道我们可以根据员工的入职日期确定他们的第一个月，但是如何在SQL中编写这个查询呢？

- Sao Tith

你可以在SQL中使用FORMAT剥离出日期，然后我们将它剥离成月份，这不难。但是，对于我来说，找出如何检查每个员工的每个月与他们的入职日期是很陌生的。 - Sao Tith

乍一看，你的第二个表设计是错误的。是否有可能这样更改它：销售（EmpId，SalesDate，Sale）？ SalesDate 应该是每个月的第一天。 - Maciej Los

顺便说一句：Access不支持MS SQL Server的功能（ROW_NUMBER()函数）。因此，“转换”StartDates和SalesMonths到相应的月份集合{MONTH1，MONTH2，等}有点复杂。 - Maciej Los

请详细说明第二个表格应该如何再次显示？ - Sao Tith

让我们在聊天中继续这个讨论。 - Maciej Los

显示剩余4条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Maciej Los · Accepted Answer

正如我在对问题的评论中提到的那样，您需要按照以下方式更改第二个表格的设计（伪代码）：

CREATE TABLE Sales(
    SaleId Autonumber PK
    [Employee ID] FK (reference to Employees table)
    SalesDate DateTime
    Sales Decimal/Double
);

然后你就能以以下方式保存你的数据：

SaleID  EmpId   SalesDate   Sales
1       2234    2014-01-01  199,99
2       1234    2013-12-01  455.99
3       1234    2014-01-01  332.32
4       2234    2014-02-01  130
5       1234    2014-02-01  334
6       2234    2014-03-01  300
7       1234    2014-03-01  553
8       2234    2014-04-01  230.99
9       3333    2014-04-01  204.02
10      1234    2014-04-01  334.99

最后，您的数据透视表可能如下所示：

TRANSFORM Sum(S.Sales) AS SumOfSales
SELECT E.[Employee Id], E.[Employee Name]
FROM Employees AS E INNER JOIN SalesByMY AS S
     ON E.[Employee Id] = S.[Employee Id]
GROUP BY E.[Employee Id], E.[Employee Name]
PIVOT 'Month-' & DateDiff('m',[E].[StartDate],[S].[SalesDate])+1;

结果如下：

EmpId   EmpName        Month-1  Month-2 Month-3 Month-4 Month-5
1234    Bobby Wilson   455.99   332.32  334     553     334.99
2234    John Smith     199.99   130     300     230.99  
3333    Jane Doe       204.02