Joining 2 tables with more then one join

[kisnay82]

Hi
I have 2 tables, in both tables there is week number and employee id, i need to join them both up, but keep getting the error

[h=1]The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.[/h]

 

[Joe4]

Do you have other tables and other joins in this query? If so, post the SQL for the query before you are trying to add this step.

If not, I am guessing that you have not selected the same type of Join for each field. They BOTH have to be the same (whether it is Inner, Left, Right, etc).

 

[kisnay82]

The sql i currently have is

SELECT qry1.[Week Num], qry1.Text, qry1.EmployeeName, qry1.ResID, tblAnnualLeave.Hours, tblAnnualLeave.Week, tblAnnualLeave.ResIDFROM tblAnnualLeave RIGHT JOIN qry1 ON tblAnnualLeave.Week = qry1.[Week Num];

i want to add qry1.ResID = tblAnnualLeave.ResID

 

[xenou]

i want to add qry1.ResID = tblAnnualLeave.ResID

what was the actual sql you created when you added this. It doesn't look quite like you should have any problems just adding a second condition here.

 

[Joe4]

Assuming that your first query works, make this change:

[COLOR=#333333]SELECT qry1.[Week Num], qry1.Text, qry1.EmployeeName, qry1.ResID, tblAnnualLeave.Hours, tblAnnualLeave.Week, tblAnnualLeave.ResID 
FROM tblAnnualLeave 
RIGHT JOIN qry1 
ON (tblAnnualLeave.Week = qry1.[Week Num]) [/COLOR][COLOR=#ff0000]AND (tblAnnualLeave.ResID = qry1.ResID)[/COLOR][COLOR=#333333];[/COLOR]

 

[kisnay82]

Thanks Joe4 that worked a treat