Geek asks and answers

Multiple (left, cross?) JOIN in MS Access

I have the following MS Access (2010) database:

database diagram

The Employee, Period, and Operation tables are populated, and the UnitOfWork table is empty.

Basically, I have some items that are produced by several workers (workers). Each element requires several operations (each worker can perform any operation of any element as many times as necessary). All operations have a certain cost, and I will need to generate reports on how much each employee earned “this month,” whether he completed the plan, etc.

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

, Employee, Period Operation LEFT JOIN UnitOfWork , OperationsDone UnitOfWork. WHERE ORDER,

, CROSS JOIN ( MS Access ), - :

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

, " JOIN".

:

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

2 3, , "JOIN Expression not supported".

- , , SQL.

!

: , , ? CROSS (, ) , LEFT, UnitOfWork ... JOIN " "? ?

+3
1

:

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
+1

More articles:


All Articles