Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    13

    Unanswered: The specified field could refer to more than one table listed in the FROM clause...

    Hey all,

    I have a query that outputs an id into a new table but not the corresponding name and address. Is there a way to output the corresponding name and address with the id into the new table? I get the following error "The specified field names_1 could refer to more than one table listed in the FROM clause of your SQL statement" with this:

    Code:
    INSERT INTO KeepThese ( ID )
    SELECT ID 
    FROM (SELECT Min(P.ID) AS ID 
    FROM [The Final Query] as P
    GROUP BY names_1, addresses
    HAVING COUNT(*) >=5
    UNION ALL
    SELECT ID, names_1, addresses FROM [The Final Query] as P
    INNER JOIN
    (SELECT Names_1, addresses
    FROM [The Final Query]
    GROUP BY names_1, addresses
    HAVING COUNT(*) < 5) as ThoseLessThan5
    ON ThoseLessThan5.Names_1 = P.Names_1
    AND ThoseLessThan5.addresses = P.addresses
    )  AS [%$##@_Alias];

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Fully qualify the column name:
    <Table>.<Column>
    Have a nice day!

  3. #3
    Join Date
    Oct 2010
    Posts
    1
    That means you have a field that has the same name in 2 tables. In that case you must indicate which table the field is coming from. For example you have to identify what table the fields names_1, addresses comes from. ie >> Table.name_1, Table.addresses

  4. #4
    Join Date
    Dec 2010
    Location
    USA
    Posts
    2
    I have alwys found that making sure your field names vary among tables is a very good time saver. For example, if I used a name in two tables, I would identify them as MainName in one table and MainName2 in the second. Doing so eliminates that error message.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Actually, you should keep them the same and fully qualify, that way when you move out of access and into an RDBMS, you can define Referential Integrity and keep everyone straight on what is what.

  6. #6
    Join Date
    Dec 2010
    Location
    USA
    Posts
    2
    Hummm, I never thought of that. Good point. As I am not that familiar with too many other dbases, I guess it hadn't crossed my mind. Thanks for the advice. I guess I should stop being lazy. LOL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •