Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: creating alias for table names in a an Append query

    wanting to know how to create aliases for the two tables
    1. NewTasksBeingAdded
    2. [table Excel data]

    for the follwing sql statement
    From what I understand the where clause (re written from joins to the where ) will not execute without the tablenames being aliased

    StrSQL = "INSERT INTO NewTasksBeingAdded ( [Activity ID], [WBS Code], [Activity Name], Area, Line) " _
    & "SELECT [TASK Excel Data].[Activity ID], [TASK Excel Data].[WBS Code], [TASK Excel Data].[Activity Name], " _
    & "[TASK Excel Data].Area, [TASK Excel Data].Line " _
    & "FROM [TASK Excel Data], NewTasksBeingAdded " _
    & "where [TASK Excel Data].[Activity ID] = TASK_BackUP.[Activity ID] " _
    & "and TASK_BackUP.[Activity ID] Is Null"
    Dale Houston, TX

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Once more, TASK_BackUP is not referenced in the FROM clause of the SQL statement. If you replace an INNER JOIN by a WHERE, you must reference both table in the FROM clause of the query.
    Code:
    StrSQL = "INSERT INTO NewTasksBeingAdded ( [Activity ID], [WBS Code], [Activity Name], Area, Line) " _
    & "SELECT [TASK Excel Data].[Activity ID], [TASK Excel Data].[WBS Code], [TASK Excel Data].[Activity Name], " _
    & "[TASK Excel Data].Area, [TASK Excel Data].Line " _
    & "FROM [TASK Excel Data], TASK_BackUP " _
    & "where [TASK Excel Data].[Activity ID] = TASK_BackUP.[Activity ID] " _
    & "and TASK_BackUP.[Activity ID] Is Null"
    Moreover the logical AND operator makes little sense:
    [TASK Excel Data].[Activity ID] cannot be both equal to TASK_BackUP.[Activity ID] AND be NULL at the same time.
    Have a nice day!

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Believe it or not wnen I did this via access in an "unmatched" query it did make since - baically i aapended all records from one table where they did not exist in another

    i finallyy got everything working It was just a matter of listing both data sets in the from and referencing the congruency of the two fields in the where

    Thank you.
    Dale Houston, TX

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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