SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS Access Append Query

    How can I append/Insert/Update data to a table?

    I have a table that 3000 records. Many of the records come from an Excel file so the "Source" field has a value of "Excel ss 2483". I would like to add a new record into another table (customerCodeIndex) for each record in the customer table that has a source = "Excel ss 2483". These new records will be just two fields: customerID and a customercode of "BASE"

    My SQL should look something like this:

    Insert into CustomerCodeIndex
    Set CustomerCodeIndex.ID= Customer.ID, CustomerCodeIndex.CodeID = "BASE"
    FROM Customer INNER JOIN CustomerCodeIndex ON Customer.ID = CustomerCodeIndex.ID
    Where Customer.Source = "Excel SS 2483"

    So far all Access wants to do is change existing records, I need it to add new records.

    I have attached to images of my relationship table and my append query.

    Thanks

    Matt
    Attached Images Attached Images

  2. #2
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I posted this question on another site and got the answer.

    Here is what Stev (from another forum) wrote:

    The SQL should be:

    INSERT INTO CustomerCodeIndex ( ID, CodeID )
    SELECT Customer.ID, "BASE" AS Expr1
    FROM Customer
    WHERE (((Customer.Source)="Excel SS 2483"));

    When you use query builder, put the source tables in the top area and it will ask you for the target table. In this case there is no need to have the target table displayed with the join. It only complicates things unneccessarily.
    Thanks to all,

    Matt


Bookmarks

Posting Permissions

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