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

concept of error
error thesaurus
error definition in computer
error definition chemistry
how to pronounce error
what is error and types of error
error formula
full of error

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")
    ;

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
...

Error, Error definition, a deviation from accuracy or correctness; a mistake, as in action or speech: His speech contained several factual errors. See more. Error definition is - an act or condition of ignorant or imprudent deviation from a code of behavior. How to use error in a sentence. Synonym Discussion of error. an act or condition of ignorant or imprudent deviation from a code of behavior…

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");

Error, on the side of caution than jump in without knowing all the facts.” Synonyms for errors at Thesaurus.com with free online thesaurus, antonyms, and definitions. Find descriptive alternatives for errors.

The specified field reference could refer to more than one table listed in the FROM clause of your SQL statement. In the following example, the OrderID field exists in both the Orders and Order Details tables:

SELECT OrderID 
FROM Orders, [Order Details];

Because the statement does not specify which table OrderID belongs to, it produces this error. To complete this operation, fully qualify the field reference by adding a table name. For example:

SELECT Orders.OrderID 
FROM Orders, [Order Details];

what is error? types of error?, something done or written by accident that is not correct, not accurate, or does not give the right result: make/introduce an error Can I amend my  An error (from the Latin error, meaning "wandering") is an action which is inaccurate or incorrect. In some usages, an error is synonymous with a mistake. In statistics, "error" refers to the difference between the value which has been computed and the correct value.

error vs err | Common Errors in English, NounEdit. error (countable and uncountable, plural errors). (uncountable) The state, quality, or condition of being wrong. quotations ▽. 1913, The Inland printer:​. 49 synonyms of errors from the Merriam-Webster Thesaurus, plus 66 related words, definitions, and antonyms. Find another word for errors. Errors: an unintentional departure from truth or accuracy.

Error, Synonyms for error at Thesaurus.com with free online thesaurus, antonyms, and definitions. Find descriptive alternatives for error. P ress the Windows key, type Network problems, and then select Identify and repair network problems in the list.; Follow the steps in the wizard to diagnose and resolve your network problems.

ERROR, What does error mean? error is defined by the lexicographers at Oxford Dictionaries as A mistake. The following example uses @@ERROR with @@ROWCOUNT to validate the operation of an UPDATE statement. The value of @@ERROR is checked for any indication of an error, and @@ROWCOUNT is used to ensure that the update was successfully applied to a row in the table.

Comments
  • Do you have a copy of MS Access? If so, why not try the query design window? Some of your ideas are not correct.
  • Thanks! I selected your answer for both the solution and the use of table_name.* - thanks!
  • Thanks for the solution! You and PinnyM had the same solution, at the same time, so I struggled with which answer to select. Pinny also used the wildcard for the fields within an alias, e.g. Service_Spec.*, so I ultimately went with his. Thank you again for your help!
  • That makes total sense, thank you!