Error msg opening query RE FROM clause

[painterz]

Hello All,

I'm trying to open a query and I keep getting the same error message:

"The specified table 'tblPeople.AddressID' could refer to more than one table listed in the FROM clause of the SQL statement."

It seems to me tblPeople.AddressID is very specific and can't be confused for anything else. So I surrounded it in brackets, [tblPeople].[AddressID], and got the same error message including the brackets. I checked and there is only one tblPeople and there is only one field called AddressID contained in tblPeople. (tblPeople.AddressID is only listed in the SQL statement once. qryDirectory1All works fine when opened. AddressID is a PK in tblAddress.) So what's causing the confusion?

Here's my SQL statement:

SELECT make_DirectoryName(IIf([tblPeople_1].[PKRelationship]=1,[tblPeople_1].[FirstName]),IIf([tblPeople_1].[PKRelationship]=1,[tblPeople_1].[LastName]),[qryDirectory2_spouse].[FirstName],[qryDirectory2_spouse].[LastName]) AS DirectoryName, qryDirectory1All.Address, tblFamily.AddressID, qryDirectoryPhoneAll.PhoneType, qryDirectoryPhoneAll.PhoneNumber, tblFamily.OwesHOA, tblFamily.Renter, tblFamily.Owner, tblPeople.EmailAddress, tblFamily.ListAddress, tblPeople_1.[Opt-in]


FROM ((((tblPeople LEFT JOIN qryDirectory2_spouse ON tblPeople.FamilyID = qryDirectory2_spouse.FamilyID) INNER JOIN tblFamily ON tblPeople.FamilyID = tblFamily.FamilyID) INNER JOIN tblPeople AS tblPeople_1 ON tblPeople.FamilyID = tblPeople_1.FamilyID) INNER JOIN qryDirectory1All ON tblPeople.AddressID = qryDirectory1All.AddressID) LEFT JOIN qryDirectoryPhoneAll ON tblPeople.FamilyID = qryDirectoryPhoneAll.FamilyID


WHERE (((tblPeople.PKRelationship)=1 Or (tblPeople.PKRelationship)=2) AND ((tblPeople_1.PKRelationship)=1))

GROUP BY make_DirectoryName(IIf([tblPeople_1].[PKRelationship]=1,[tblPeople_1].[FirstName]),IIf([tblPeople_1].[PKRelationship]=1,[tblPeople_1].[LastName]),[qryDirectory2_spouse].[FirstName],[qryDirectory2_spouse].[LastName]), qryDirectory1All.Address, tblFamily.AddressID, qryDirectoryPhoneAll.PhoneType, qryDirectoryPhoneAll.PhoneNumber, tblFamily.OwesHOA, tblFamily.Renter, tblFamily.Owner, tblPeople.EmailAddress, tblFamily.ListAddress, tblPeople_1.[Opt-in]

ORDER BY make_DirectoryName(IIf([tblPeople_1].[PKRelationship]=1,[tblPeople_1].[FirstName]),IIf([tblPeople_1].[PKRelationship]=1,[tblPeople_1].[LastName]),[qryDirectory2_spouse].[FirstName],[qryDirectory2_spouse].[LastName]), qryDirectoryPhoneAll.PhoneType DESC;

Thanks

 

[plog]

My guess it has to do with tblPeople_1. Perhaps the alias isn't used in time it encounters that code. Access makes such a mess of JOINs with all their unneeded parenthesis I can't be too certain.

My advice is to rip out tblPeople_1 and see if the query works. If so, that's your culprit. To fix it I would make a query on tblPeople, name it anything, then bring that into your query to replace tblPeople_1.

 

[painterz]

Hi Plog, I'm glad you responded. This query was based on your work you did for me for my directory. Everything was working great 2 months ago, I even printed a directory and sent this report (based on the above query) to my neighborhood. Since then I've been working on integrating the new tblFamily into the forms and haven't touched the reports or underlying queries.

Now, my reports aren't working. I'll work on rewriting my SQL statement. My directory query is giving me a similar error message.

Thanks

 

[painterz]

Instead of stripping out tblPeople_1, I used a query based on tblPeople with the same fields in the query. I still got the same error message regarding AddressID. Wouldn't replacing the reference to the table with a query rule out it's tblPeople_1 causing the problem? Access has been so weird lately, I'm wondering if it's software.

 

[plog]

What did sounded like my proposed solution for if it was tblPeople_1 causing the issue. You really need to make sure that's the case though.

In your original query, remove all references to tblPeople_1 and then see if the resulting query works. Don't replace them with a query of a new name, completely remove it from the query.

 

[painterz]

Hi plog,

Getting rid of tblPeople_1 also gets rid of calling the function make_DirectoryName. So with both of those gone, I still get the same error regarding AddressID.

I don't get it, it's only referenced once in the FROM clause. What's there to cause confusion???

BTW, my directory query has the same error message. I've looked at my original db tables and I can't see the difference.

Thanks

 

[painterz]

Head smack!!! I dropped AddressID from tblPeople because it was no longer relevant; it is still in my older design. It would have been nice if Access had just said the field didn't exist.