Specified Field Could Refer to more than one table

[jjlogan]

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

 

[Andrzejek]

I would try something like:
[tt]
strSQL = "SELECT " & strJT & ".FICSYr, " & strJT & ".RespOrg[blue] As MyUniqueRespOrg[/blue], [RespOrgs].[RespOrgName] ...
[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[jjlogan]

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

 

[dhookom]

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

 

[jjlogan]

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