none
the specified field 'raterRank' could refer to more than one table listed in the FROM clause of your SQL statement RRS feed

  • Question

  • I know this is a common question, and I know I am probably doing this wrong. In our organization we have an employee and he has a rater, senior rater, and rating reviewer. I created a seperate table called tblRaters and have the raterID, raterFN, raterLN, raterRank raterEmail.

    In the tblEmployee I have the employees info and then raterID, srRaterID, and reviewerID. Since anyone listed on the raters table can be a rater, senior rater or reviewer, I thought I may be able to just make a relationship from the raterID to each of the raterID, srRaterID, and reviewerID.

    When i try to run a query I get the error "the specified field 'raterRank' could refer to more than one table listed in the FROM clause of your SQL statement" Any thoughts on how to change my table to fix this issue?

    Thursday, August 3, 2017 8:34 PM

All replies

  • What is the SQL of your query?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, August 3, 2017 8:47 PM
  • Hi,

    Without actually seeing your table, I didn't want to make a guess. However, this error can usually be fixed by simply adding the table's name into the field. For example:

    SELECT TableName.FieldName
    FROM TableName
    INNER JOIN OtherTableName
    ON TableName.Field1=OtherTableName.Field2

    Hope it helps...

    Thursday, August 3, 2017 8:52 PM
  • SELECT TableName.FieldName
    FROM TableName
    INNER JOIN OtherTableName
    ON TableName.Field1=OtherTableName.Field2

    Hi cartotech81,

    I am in line with .theDBguy's advise, but things become more simple if you use an alias

    SELECT TableName.FieldName AS FieldName
    FROM TableName
    INNER JOIN OtherTableName
    ON TableName.Field1=OtherTableName.Field2

    Imb.

    Thursday, August 3, 2017 9:32 PM
  • Hi cartotech81,

    Did the suggestion from .theDBguy and Imb-hb work for you?

    If they did, I would suggest you mark the helpful reply as answer.

    If not, could you share us your simple database, we will try to reproduce your issue.

    Regards,

    Tony


    Help each other

    Monday, August 14, 2017 8:10 AM
  • I missed your post the first time around, but my guess is that you are trying to join the employees table to a single instance of the raters table.  You should join the former to three separate instances of the latter.  In query design view you would do this by adding the raters table three times to the design window.  You can then join the employees table separately to each instance of the raters table, each of which will be given a separate alias by adding a suffix number to the table name.  You might want to change these to more meaningful aliases in SQL view.

    If each employee always has a rater, senior rater, and rating reviewer you can use INNER JOINS, but if not you should use LEFT OUTER JOINS, otherwise any employees without all three will not be returned by the query.

    The same-named columns form each of the three instances of the raters table will be qualified by the table aliases in the result table, but again you might wish to give each a more meaningful alias, which can be done either in query design view or SQL view.

    Ken Sheridan, Stafford, England

    Monday, August 14, 2017 10:30 PM
  • Hi cartotech81,

    I can see that after creating this thread, you did not follow up this thread again.

    this thread is still open.

    If your issue is solved now then I suggest you to update the status of this thread and post the solution here and mark it as an answer.

    if the issue is still exist then I suggest you to refer the suggestion provided by the community members. it can solve your issue.

    if still you have any further question regarding the above question then let us know about that.

    we will try to provide further suggestion to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 22, 2017 8:10 AM
    Moderator