Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pretoria
Starting Member
6 Posts |
 Posted - 2005-07-23 : 10:20:05
|
| 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; --------------- |
|
|
giovi2002
Starting Member
46 Posts |
Posted - 2005-07-23 : 12:27:22
|
| I can't see the jpg Your from clause looks special probably you refer to another query which also has candidates, just rename candidates into candidates As Can in your query , copy the query with a testname and replace the from clause with this FROM Consultants INNER JOIN (CandidateStatus INNER JOIN (Candidates As Can INNER JOIN (CandidateCustomer INNER JOIN [Join Query] ON CandidateCustomer.CanCustID = [Join Query].CanCustID) ON Can.CandidateID = CandidateCustomer.CandidateID) ON CandidateStatus.AccStatusID = CandidateCustomer.Status) ON Consultants.ConsultantID = Can.ConsultantID; In your select statement replace all candidates declarations with Can like thisselect can.firstname, can.lastname etcetera |
 |
|
|
pretoria
Starting Member
6 Posts |
Posted - 2005-07-25 : 11:19:46
|
| great! Problem solved. |
|
|
|
|
|
|
|
|
SQL statement gives error on report creation