Joining tables

[pranavnegandhi]

I am building a simple task tracker to keep a record of tasks being passed back and forth between our team. The schema is fairly simple as illustrated in the image below.

I want a recordset as follows out of this database (+/- few columns. But these are the most essential ones needed) -

ixProject | Project.sTitle | ixTask | Task.sTitle | TaskHistory.dtEntry | sStatus | sEmployeeAssignor | sEmployeeAssignee

This is basically a recordset of the history of the task (who assigned task to whom) and the status (assigned, resolved, closed). I can get the query to return the user id (ixEmployee) without any problems. But to display this in the interface I will need the employees name (sUsername).

I tried the following query -

SELECT Employee.sUsername AS sEmployeeAssignor, Employee.sUsername AS sEmployeeAssignee, Project.sTitle AS sProjectTitle, Task.sTitle, TaskHistory.dtEntry, TaskHistory.sDescription FROM
(
	(
		(Project INNER JOIN Task ON Project.ixProject = Task.ixProject) 
		INNER JOIN 
		(Employee INNER JOIN TaskHistory ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignor)
		ON Task.ixTask = TaskHistory.ixTask
	)
	INNER JOIN Employee ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignee <<< THIS IS THE PROBLEM LINE
)
WHERE (SELECT LAST(TaskHistory.ixEmployeeAssignee) FROM TaskHistory) = 1
ORDER BY TaskHistory.ixTask, TaskHistory.ixTaskHistory

This is the error I get -
Join expression not supported.
State:S1000,Native:-3530,Origin:[Microsoft][ODBC Microsoft Access Driver]

Can someone explain how I can solve this problem? I am currently running two queries - one to retrieve the usernames, and the other to retrieve the history with the user id's - and replacing the user id's with usernames when displaying the records, but would like to have a cleaner solution.

 

[Moniker]

You are trying to INNER JOIN on the same two tables in both directions, which is an unsupported join. Look at your query again. Specifically, this is wrong and will not work (note the circular INNER JOINs.)

    INNER JOIN Employee 
       (INNER JOIN TaskHistory 
           ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignor)
        ON Task.ixTask = TaskHistory.ixTask
    INNER JOIN Employee 
        ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignee

 

[pranavnegandhi]

Thanks for your reply.

I understood what is wrong with my query. But I still don't get it how to get the right one. I'm going ahead with replacing employee id's at runtime with the username, but it would be good to know the solution to this problem.