Hi,
I'm having a problem I just can't seem to solve. I've created a query that includes a join query as well. When I run the query it displays the data correctly so no problem here. But as soon as I want to create a report based on that query I get the following error when trying to run the report.
"The specified field "Candidates.Firstname" coud refer to more than one table listed in the FROM clause of your SQL statement"
I've consulted the help function in this but it doesn't give me enough information.
The SQL statement is as follows: Does anyone have a clue what I could do to get this running in my report? It strikes me as odd that the query run does work but basing the report on this same statement produces an error.
I'VE ATTACHED A JPEG FILE TO VIEW THE RELATIONSIPS ON THIS QUERY.
---------------
SELECT Candidates.FirstName, Candidates.LastName, [Join Query].Customer, CandidateCustomer.CustomerID, CandidateStatus.Status, CandidateCustomer.LastDateAction, CandidateCustomer.Amount, Consultants.ConsultantID, Consultants.FirstName, Consultants.LastName
FROM Consultants INNER JOIN (CandidateStatus INNER JOIN (Candidates INNER JOIN (CandidateCustomer INNER JOIN [Join Query] ON CandidateCustomer.CanCustID = [Join Query].CanCustID) ON Candidates.CandidateID = CandidateCustomer.CandidateID) ON CandidateStatus.AccStatusID = CandidateCustomer.Status) ON Consultants.ConsultantID = Candidates.ConsultantID;
---------------
I'm having a problem I just can't seem to solve. I've created a query that includes a join query as well. When I run the query it displays the data correctly so no problem here. But as soon as I want to create a report based on that query I get the following error when trying to run the report.
"The specified field "Candidates.Firstname" coud refer to more than one table listed in the FROM clause of your SQL statement"
I've consulted the help function in this but it doesn't give me enough information.
The SQL statement is as follows: Does anyone have a clue what I could do to get this running in my report? It strikes me as odd that the query run does work but basing the report on this same statement produces an error.
I'VE ATTACHED A JPEG FILE TO VIEW THE RELATIONSIPS ON THIS QUERY.
---------------
SELECT Candidates.FirstName, Candidates.LastName, [Join Query].Customer, CandidateCustomer.CustomerID, CandidateStatus.Status, CandidateCustomer.LastDateAction, CandidateCustomer.Amount, Consultants.ConsultantID, Consultants.FirstName, Consultants.LastName
FROM Consultants INNER JOIN (CandidateStatus INNER JOIN (Candidates INNER JOIN (CandidateCustomer INNER JOIN [Join Query] ON CandidateCustomer.CanCustID = [Join Query].CanCustID) ON Candidates.CandidateID = CandidateCustomer.CandidateID) ON CandidateStatus.AccStatusID = CandidateCustomer.Status) ON Consultants.ConsultantID = Candidates.ConsultantID;
---------------
