Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating and appending with the same table (2000)

    People are sending me Excel tables, filling in the blanks where I have missing information in some fields. I import this table into Access. Somebody kindly told me how to create an update query by matching the first and last name fields with the master table. That worked like a charm. However, my friends are also adding new people to the bottom of the Excel table. What I need Access to do is go through the names in both tables, find the ones that don't match, then append the ones in the imported Excel table to the master table.

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

    Re: Updating and appending with the same table (2000)

    Using the same table and field names as in the database you attached to <post#=441865>post 441865</post#>, you can create a query with the following SQL:

    INSERT INTO TblAlumni
    SELECT tbleUpdatedInfo.*
    FROM TblAlumni RIGHT JOIN tbleUpdatedInfo ON (TblAlumni.Surname = tbleUpdatedInfo.Surname) AND (TblAlumni.FirstName = tbleUpdatedInfo.FirstName)
    WHERE (((TblAlumni.Surname) Is Null) AND ((TblAlumni.FirstName) Is Null));

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating and appending with the same table (2000)

    I tried it, but it didn't seem to work. I created the query like you did in the diagram, but I didn't insert the SQL text, because I didn't know where to put it or if it was merely a description of what was happening.

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

    Re: Updating and appending with the same table (2000)

    The SQL and the screenshot are two different ways of looking at the same query. You can view/edit the SQL for a query by opening it in design view, then selecting View | SQL.
    If you still have problems, please provide details on how "it didn't seem to work".

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating and appending with the same table (2000)

    I got a message saying "duplicate output destination "FirstName".

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

    Re: Updating and appending with the same table (2000)

    Access will automatically fill in all field names it can match in the 'Append to' line. You must manually clear the field names in the 'Append to' line for the columns used for selecting the correct records - this has been done in the screenshot I appended to my first reply. If you don't clear them, the first and last name will occur twice - once implicitly, as part of TblAlumni.* and once explicitly. This will cause the error message you got.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating and appending with the same table (2000)

    Ah, yes, I saw the difference and cleared them. I have a screen capture in Word of the what I did. It still doesn't work.

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

    Re: Updating and appending with the same table (2000)

    You haven't changed the join between the tables into an outer join. Double click one of the lines and select the option to display ALL records from ed and only related records from TblAlumni, then click OK. Repeat for the other line. Both lines should now have an arrow pointing towards TblAlumni.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating and appending with the same table (2000)

    You are clever, aren't you? Thanks a lot.

Posting Permissions

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