将行数据访问到基于关闭ID的列 [英] Access Row Data to Columns Based Off ID

问题描述

我有一个用于员工培训的MS Access数据库，有一个班级，要求每个员工每年参加两次.

I have a MS Access database for employee training and there is a class that each employee is required to have twice a year.

该类的表如下:

EmployeeID  ClassDate   ClassHours
1           1/1/2011        8
1           7/31/2011       7
2           2/1/2011        8
2           8/31/2011       7
3           3/1/2011        8
3           9/30/2011       7   

我希望表格的格式如下:

I want the table to be formated like this:

EmployeeID  ClassDate_1 ClassHours_1    ClassDate_2 ClassHours_2
1           1/1/2011        8           7/31/2011       7
2           2/1/2011        8           8/31/2011       7   
3           3/1/2011        8           9/30/2011       7   

如何编写查询以根据EmployeeID将第二节课的日期和小时字段移到同一行?

How do I write a query to move the second class date and hour fields to the same row based off the EmployeeID?

我已经通过该网站进行了研究，对于我想要达到的目标，所有可能的解决方案似乎都过于复杂.

I have researched through this site and all the possible solutions appear to be overly complicated for what I am trying to acheive.

非常感谢您的帮助.

谢谢

推荐答案

您将不得不创建带有子选择的查询

You will have to create a query with a sub-select

SELECT
    X.EmployeeID,
    X.d1 AS ClassDate_1, ec1.ClassHours AS ClassHours_1,
    X.d2 AS ClassDate_2, ec2.ClassHours AS ClassHours_2
FROM
    ( (SELECT e.EmployeeID, Min(e.ClassDate) AS d1, Max(e.ClassDate) AS d2
       FROM employee_classes AS e
       GROUP BY e.EmployeeID) AS X
      INNER JOIN employee_classes AS ec1
          ON X.EmployeeID = ec1.EmployeeID AND X.d1 = ec1.ClassDate
    )
    INNER JOIN employee_classes AS ec2
        ON X.EmployeeID = ec2.EmployeeID AND X.d2 = ec2.ClassDate;

---

或者，您可以将嵌套选择存储为查询(将其称为query1):

SELECT e.EmployeeID, Min(e.ClassDate) AS d1, Max(e.ClassDate) AS d2
FROM employee_classes AS e
GROUP BY e.EmployeeID

然后在第二个查询中使用它

and then use it in a second query

SELECT
    X.EmployeeID,
    X.d1 AS ClassDate_1, ec1.ClassHours AS ClassHours_1,
    X.d2 AS ClassDate_2, ec2.ClassHours AS ClassHours_2
FROM
    ( query1 AS X
      INNER JOIN employee_classes AS ec1
          ON X.EmployeeID = ec1.EmployeeID AND X.d1 = ec1.ClassDate
    )
    INNER JOIN employee_classes AS ec2
        ON X.EmployeeID = ec2.EmployeeID AND X.d2 = ec2.ClassDate;

---

如果不显示小时数会更容易

---

It would be much easier if the hours were not displayed

SELECT e.EmployeeID, Min(e.ClassDate) AS ClassDate_1, Max(e.ClassDate) AS ClassDate_2
FROM employee_classes AS e
GROUP BY e.EmployeeID

---

实际上，有一个更简单的解决方案，但是它假定该表按EmployeeID和ClassDate排序.这种假设并不安全，因为无法保证自然排序. Access可以随时决定"以其他方式重新组织记录.

---

Indeed there is a simpler solution, however it assumes that the table is sorted by EmployeeID and ClassDate. This assumption is not safe, as no natural sort order is guaranteed. Access can "decide" to reorganize the records in a different way at any time.

SELECT
    EmployeeID,
    First(ClassDate) AS ClassDate_1, First(ClassHours) AS ClassHours_1,
    Last(ClassDate) AS ClassDate_2,  Last(ClassHours) AS ClassHours_2
FROM
    employee_classes
GROUP BY
   EmployeeID
ORDER BY
   EmployeeID;

再次，子选择可以提供帮助

Here again a sub-select can help

SELECT
    EmployeeID,
    First(ClassDate) AS ClassDate_1, First(ClassHours) AS ClassHours_1,
    Last(ClassDate) AS ClassDate_2,  Last(ClassHours) AS ClassHours_2
FROM
    (SELECT * FROM employee_classes ORDER BY EmployeeID, ClassDate)
GROUP BY
   EmployeeID
ORDER BY
   EmployeeID;

但是，需要子查询或第二个查询.

Howsoever, either a sub-query or a second query is required.

这篇关于将行数据访问到基于关闭ID的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！