CODEWITHSUNDEEP
sqlms-accessjoin
Ubeogesh
Ubeogesh

Reputation: 1870

Multiple (left, cross?) JOINs in MS Access

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

database diagram

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.

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

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

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

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

I've also tried like this:

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

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

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

Thanks in advance!

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?

Upvotes: 3

Views: 2724

Answers (1)

aF.
aF.

Reputation: 66717

Try it this way:

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

Upvotes: 1

Related Questions