×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Specified Field Could Refer to more than one table

Specified Field Could Refer to more than one table

Specified Field Could Refer to more than one table

(OP)
So much fun when have limited time and get these errors. I create an SQL in the Report-Open event. It's a simple Select query with FROM two tables INNER joined and then three other tables in LEFT JOIN. I keep getting the error that "field RespOrg could refer to more than on table in FROM clause." I know this is true and so I fixed it by prefixing with variable representing the table name and when I do Debug.Print the "TableName.RespOrg" is spelled out!! I specify the table name using a string variable strJT (JobTableYYYY) since in opening the report, the use specifies a year and each table has the year at end of table, e.g. JobDetail2013, JobDetail2014,... So, prior to the SQL create lines, I assign the variable strJT to the actual table name with the correct year at end.
[strSQL = "SELECT " & strJT & ".FICSYr, " & strJT & ".RespOrg, [RespOrgs].[RespOrgName], " & strJT & ".Job, JobDesc, ContactNew, PM_GS, Sjob, SjobDesc, UCACurrent, " _
& "SjobDateEffective, SjobDateLaborExpire, " _
& "SjobDateODCExpire, SjobScope, ReasonNewSjob, ReasonCloseSjob " _
& "FROM ((((" & strJT & " INNER JOIN " & strJST & " ON " & strJT & ".Job = " & strJST & ".Job) LEFT JOIN RespOrgs ON " & strJT & ".RespOrg = RespOrgs.RespOrg) " _
& "LEFT JOIN Contacts ON " & strJT & ".ContactID = Contacts.ContactID) LEFT JOIN PMs ON " & strJT & ".PM_GS_ID = PMS.PM_Id) " _
& "WHERE " & strJT & ".Job = '" & strJob & "'" _
& "ORDER BY RespOrg, Job, Sjob;"
Debug.Print strSQL
Me.RecordSource = strSQL][/code]
The field RespOrg is a field in strJT (JobDetail2015) for example. RespOrg is also a field in table RespOrgs in FROM clause. Debug.Print shows "Select ... JobDetail2015.RespOrg, ..."

Thanks for considering this question. Searching this forum did not help in this case.
Jeff

RE: Specified Field Could Refer to more than one table

I would try something like:

strSQL = "SELECT " & strJT & ".FICSYr, " & strJT & ".RespOrg As MyUniqueRespOrg, [RespOrgs].[RespOrgName] ...

smile


---- Andy

There is a great need for a sarcasm font.

RE: Specified Field Could Refer to more than one table

(OP)
Thanks for the input. 1) I did try your suggestion using the alias and still got the Error message: field RespOrg could refer to more than one table in the From clause. 2) So I then tried using the lookup table instead which holds the field RespOrg also; I used the full [Table name of lookup].[RespOrg] syntax and still got the same error. SO - I don't think the problem in this case is confusion over what table the field is in. I will check other possible errors (which done already) some more.
Or, if you have any further ideas, they would be welcome.
Thanks,
Jeff

RE: Specified Field Could Refer to more than one table

What do you get in the debug.print window? Have you attempted to copy and paste it into a new, blank query to see what's happening? Can you share the results?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Specified Field Could Refer to more than one table

(OP)
SOLVED: Thanks for the quick responses folks. Duane, I tried copying the Debug.print and got the same error messages, but in the sql copied I saw the error... stood out like a sore thumb - It was in the ORDER BY clause. I had an unassigned field RespOrg (cause of error); this field reference did not have table assignment. I was just looking in the Select clause. Your ideas kept me opening up new windows (so to speak) and that led to the solution.
Sorry, for inconvienence for you folks - but I did learn from this.
I am NOT a programmer (full-time) but a Tech User and can't find out how to change the tag associated with my name.
So appreciate your help!
Jeff

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close