MS Access中的多个(左，交叉?)联接 [英] Multiple (left, cross?) JOINs in MS Access

问题描述

我有以下MS Access(2010)数据库:

I've got a following MS access (2010) database:

Employee，Period和Operation表已填充，而UnitOfWork表为空白.

Employee, Period and Operation tables are populated, and UnitOfWork table is blank.

基本上，我有一些物品是由多名工人(雇员)生产的.每个项目都需要执行多项操作(每个工作人员可以在任意时间多次执行任何项目的任何操作).所有操作都需要一定的成本，我将需要生成有关每个员工本月"的收入，他是否完成了计划等的报告.

Basically I am having some items being produced by multiple workers (employees). Each item requires multiple operations (each worker can do any operations of any item any amount of times). All operations have some cost, and I will need to generate reports of how much every employee have earned "this month", have he fulfilled the plan, etc.

我想进行一次选择以查看以下内容:UnitOfWorks，适用于Employee，Period和Operation的每个可用组合.

I want to do a SELECT to see following: UnitOfWorks, for every available combination of Employee, Period and Operation.

换句话说，我想选择Employee，Period和Operation以及LEFT JOIN UnitOfWork的所有可能组合作为结果，因此我可以为每个UnitOfWork输入OperationsDone .我将添加WHERE子句和ORDER及更高版本

In other words, I want to SELECT all possible combinations of Employee, Period and Operation and LEFT JOIN UnitOfWork to the result, so I can input OperationsDone for every UnitOfWork. I'll add WHERE clauses and ORDER and later

我认为CROSS JOIN会有所帮助(在MS Access中只是用逗号枚举)，如下所示:

I thought CROSS JOIN would help (In MS Access it is just enumeration with commas), something like this:

SELECT * FROM (Employee, Period, Operation)
LEFT JOIN UnitOfWork on UnitOfWork.OperationId=Operation.OperationId

但是执行此查询时，出现"JOIN操作中的语法错误"错误.

But when I execute this query, I get a "Syntax error in JOIN operation" error.

我也尝试过这样:

SELECT * FROM Employee AS e LEFT JOIN
(Period AS p LEFT JOIN UnitOfWork AS uow ON p.PeriodId=uow.PeriodId)
on e.EmployeeId=uow.EmployeeId

这些仅是我需要的3个连接中的2个，但与此同时，我已经收到不支持JOIN表达式"错误.

These are only 2 joins out of 3 I need, but already with this I get "JOIN Expression not supported" error.

任何数据库设计建议也都受到赞赏-这是我正在设计的新数据库，而且我是SQL新手.

Any database design suggestions are also appreciated - this is a new database I'm designing and I'm a SQL newbie.

提前谢谢！

更新: 我也在想，也许我应该尝试子查询?就像在一个查询中进行(操作，员工和期间的)CROSS联接，然后将UnitOfWork联接到查询结果中一样...但是我如何按多个字段"进行联接?以及如何在访问中实现子查询?

Update: I am also thinking, maybe I should try a subquery? Like doing a CROSS join (of Operation, Employee and Period) in one query, and then LEFT joining the UnitOfWork to the query result... but how do I do a JOIN "by multiple fields"? And how do I implement a subquery in access?

推荐答案

尝试一下:

SELECT * FROM (Employee AS e LEFT JOIN UnitOfWork AS uow ON e.EmployeeId=uow.EmployeeId)
              LEFT JOIN Period AS p on p.PeriodId=uow.PeriodId

这篇关于MS Access中的多个(左，交叉?)联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！