Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append Query (2000)

    Hi,

    I have a employee table in local Access database need to update from employee table in SQL server every month. I know I have to use append query by using ODBC connection. But I would like only append the data that not in local access db but in SQL server. If I just use regular append query, it will append all the data in the employee table even some data already exit in local database. So it will cause many duplicate data.

    How can I create query that only append the data that is not exit in local access database, but in the employee table of SQL server?

    Please advice.

    Thanks

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query (2000)

    Let's say the local table is called tblEmployeesLocal and the linked SQL Server table is called tblEmployeesRemote.
    You need a field that uniquely identifies the employee, let's call it EmployeeID. It could be an AutoNumber type field, or the employee's SSN.
    Create a query based on the linked SQL Server table tblEmployeesRemote.
    Use Query | Append Query and specify the local table tblEmployeesLocal as target.
    Add the fields you want to append, and specify the corresponding local fields.
    And now the essential point: add the following in the Criteria line for the EmployeeID field:

    Not In (SELECT EmployeeID FROM tblEmployeesLocal)

    Select Query | Run to execute the query. This will be slow.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000)

    Thank you so much, Hans. It works as always.

    But I have another questions for you, there's a field of the employee table in SQL Server is "EmployeeName" which includes Last Name & First Name(Format: Last Name, First Name). However, there's "Employee Last Name," "Employee First Name," & "Employee Middle Name." fields in employee table of local access database. Is there any way I can split the "EmployeeName" filed from employee table in SQL server and append it to the Last Name, First Name & Middle Name in local database as I append the whole table.

    Thanks

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query (2000)

    If the SQL Server table contains Last Name, First Name, the Middle Name field in the local table will have to remain empty.
    Add the following calculated column to the query:
    <code>
    Last Name: Trim(Left([EmployeeName],InStr([EmployeeName],",")-1))
    </code>
    and make sure that the Last Name field is selected in the Append to line.
    Add another calculated column to the query:
    <code>
    First Name: Trim(Mid([EmployeeName],InStr([EmployeeName],",")+1))
    </code>
    and make sure that the First Name field is selected in the Append to line.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000)

    Thank you so much, Hans.

    Actually there's a middle name in the SQL Server and format should be Last Name, First Name (Space) Middle Name.

    Sorry for providing incomplete information. So should I have another calculated column for middle name?

    Thank you so much.

    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query (2000)

    Is there *always* a middle name, or do some records only have a first name?

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000)

    Hi Hans,

    Some records have middle name and some records only have a first name.

    Thanks

    Regards

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query (2000)

    It's best to compute the first and middle names in steps, otherwise it becomes very complicated. See the query in the attached database. Pos1 is the position of the comma in the EmployeeName field, and Pos2 is the position of the space between the first and middle names (it is 0 if there is no middle name).
    Attached Files Attached Files

  9. #9
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000)

    Thanks, Hans!

    I have another question for you. There's two fields (HomeAddress1 & HomeAddress 2) in SQL server. HomeAddress1 include street name and HomeAddress2 include Apart#. However I only have HomeAddress in local Access database. How can I combine the two HomeAddress fields and append in HomeAddress field as I run append query?

    Thanks

    Regards

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query (2000)

    In the append query, create a calculated column:
    <code>
    HomeAddress: [HomeAddress1] & (" "+[HomeAddress2])
    </code>
    Enter HomeAddress in the Append to line.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (2000)

    Thank you so much, Hans.

    Regards

Posting Permissions

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