Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Append Query (2003 SP2)

    I have 2 address tables (both for different functions), but am realizing that with a little other coding it would be simpler to have only one table. What is the best way to append from one table to another and have my query skip a record if it is already in the larger table? These are the fields to be appended to. The fields to be appended come from tblNewsletters with the same fields.

    tblAddresses.CustomerID,
    tblAddresses.FirstName,
    tblAddresses.LastName,
    tblAddresses.CareOf,
    tblAddresses.Address,
    tblAddresses.City,
    tblAddresses.State,
    tblAddresses.PostalCode,
    tblAddresses.Country

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

    Re: Append Query (2003 SP2)

    What determines whether a record is a duplicate? The CustomerID field, or the combination of FirstName and LastName, or ...?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append Query (2003 SP2)

    CustomerID field does. CustomerID field is made up of the Postal Code, first 3 letters of the last name, first 2 letters of the first name and than 01. Example: 67443HENJO01 would be the ID for John Henry who lives in Postal code 67443.

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

    Re: Append Query (2003 SP2)

    Open tblAddresses in design view.
    Make CustomerID the primary key.
    Close and save the table.

    When you run the append query, you'll get a message that n records cannot be appended because of a key conflict, with options to cancel or continue. Click Continue - Access will append the unique CustomerIDs and ignore the duplicates.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append Query (2003 SP2)

    Thanks. I sure was wasting time and space. When I ran the query there were only 12 records that weren't in the Addresses table.

Posting Permissions

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