ERROR "The specified field could refer to more than one table in the FROM clause"

Question

I'm using Access 2010.

My error seems to be a result of the aliasing.

The solutions I've read specify that a different alias should be used at every level (which I've done) and that Access likes lots of SELECT * (so I added those, too) but it hasn't resolved my issue.

I'm getting an error on ServiceZipSpec.Service_Product. Any pointers? Thanks!

SELECT DISTINCT ServiceZipSpec.Service_Product, ServiceZipSpec.Specificity, Service_Availability.Available, Service_Availability.Zip_Code, Service_Availability.State
    FROM 
    ( SELECT * FROM 
       ( SELECT * FROM Service_Availability AS C_Avail1
         INNER JOIN 
            (
              SELECT C_Avail2.Service_Product, MAX(C_Avail2.Specificity) AS Spec
              FROM Service_Availability AS C_Avail2
              WHERE (C_Avail2.State = "TX" OR C_Avail2.State = "CW")
              GROUP BY C_Avail2.Service_Product 
            ) MaxSpec
         ON C_Avail1.Service_Product = MaxSpec.Service_Product
             AND C_Avail1.Specificity = MaxSpec.Spec
       ) Service_Spec
    LEFT JOIN TABLE_ZipCodes ON Service_Spec.State = TABLE_ZipCodes.State
    ) ServiceZipSpec

    WHERE ServiceZipSpec.Available = TRUE AND (ServiceZipSpec.State = "TX" OR ServiceZipSpec.State = "CW")
    ;

Answer 1

The source of your problem is that you are using SELECT * when joining tables that have similarly named columns. Try limiting to SELECT table_name.*, or simply pick out the columns you need.

For example:

SELECT DISTINCT ServiceZipSpec.Service_Product, ServiceZipSpec.Specificity, Service_Availability.Available, Service_Availability.Zip_Code, Service_Availability.State
  FROM 
    ( SELECT Service_Spec.*, TABLE_ZipCodes.Zip_Code FROM 
       ( SELECT C_Avail1.* FROM Service_Availability AS C_Avail1
...

Answer 2

You need to SELECT colName1, colname2 instead of the SELECT *, you are getting a conflict in your subqueries. Also your outer select list will be from the table with the alias ServiceZipSpec:

SELECT DISTINCT ServiceZipSpec.Service_Product, 
    ServiceZipSpec.Specificity, 
    ServiceZipSpec.Available, 
    ServiceZipSpec.Zip_Code, 
    ServiceZipSpec.State
FROM 
( 
    SELECT colName1, colname2, etc -- name your columns here
    FROM 
    ( 
        SELECT colName1, colname2, etc -- name your columns here
        FROM Service_Availability AS C_Avail1
        INNER JOIN 
        (
          SELECT C_Avail2.Service_Product, MAX(C_Avail2.Specificity) AS Spec
          FROM Service_Availability AS C_Avail2
          WHERE (C_Avail2.State = "TX" OR C_Avail2.State = "CW")
          GROUP BY C_Avail2.Service_Product 
        ) MaxSpec
        ON C_Avail1.Service_Product = MaxSpec.Service_Product
            AND C_Avail1.Specificity = MaxSpec.Spec
   ) Service_Spec
    LEFT JOIN TABLE_ZipCodes 
        ON Service_Spec.State = TABLE_ZipCodes.State
) ServiceZipSpec
WHERE ServiceZipSpec.Available = TRUE 
    AND (ServiceZipSpec.State = "TX" OR ServiceZipSpec.State = "CW");