Results 1 to 5 of 5
  1. #1
    kevin.c is offline Novice
    Windows 8 Access 2007
    Join Date
    Dec 2012
    Posts
    2

    Append if record doesn't exist, update if record exists question

    Hi,



    I'm using Access 2007 and I'm trying to find the best way to copy records from a read only source table into another table. The criteria would be based on a purchase order number in each table. If the record in the source table has a purchase order number that isn't in the destination table, it would be appended to the destination table. If the purchase order number exists in both the source and destination tables I would just want to update (copy) the record from the source table to the destination table. I'm assuming I need a cross between an append query and an update query but not really sure how to create it. Any help would be appreciated.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I'm assuming I need a cross between an append query and an update query but not really sure how to create it.
    I do not know of any type of query that would do both at the same time. I think you need two queries, one Append query and one Update query.
    It shouldn't be a big deal. You can even create a macro to run both with a single click of a button.

  3. #3
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    As JoeM suggests, there is no query type that can do both, so the two operations will have to be done separately. Logically, the update query should be done first. The easiest way is to join the destination table to the read only table via the PO Number field assuming that field is your key field. This join finds the records in the two table that have the same PO Number and hence are the ones that need to be updated. Let's assume that you have 3 fields in the destination table you want to update (the customer ID field, the PO amount and the PO date). The update query would look something like this:

    UPDATE tblDestination INNER JOIN tblReadOnly ON tblDestination.PONumber = tblReadOnly.PONumber SET tblDestination.fkCustomerID = [tblReadOnly].[fkCustomerID], tblDestination.dteOrder = [tblReadOnly].[dteOrder], tblDestination.POAmt = [tblReadOnly].[POAmt];


    For the append query, we have to find those PO number that do not yet exist in the destination table. We can use the NOT EXISTS keywords in the append query as shown below
    to accomplish this:


    INSERT INTO tblDestination ( PONumber, fkCustomerID, dteOrder, POAmt )
    SELECT tblReadOnly.PONumber, tblReadOnly.fkCustomerID, tblReadOnly.dteOrder, tblReadOnly.POAmt
    FROM tblReadOnly
    WHERE NOT EXISTS (SELECT tblDestination.PONumber FROM tblDestination WHERE tblDestination.PONumber=tblReadOnly.PONumber);


    Now as to the append query.

  4. #4
    kevin.c is offline Novice
    Windows 8 Access 2007
    Join Date
    Dec 2012
    Posts
    2
    Hi, thanks for your post. It has pointed me in the right direction.

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Glad we could help out. Please post back if you have any additional questions.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-19-2012, 04:30 PM
  2. Replies: 1
    Last Post: 12-29-2011, 05:51 PM
  3. Append if record doesn't exist
    By Lorlai in forum Queries
    Replies: 1
    Last Post: 06-14-2011, 06:38 PM
  4. If record does not exist, open add new record form
    By RobertIngles in forum Programming
    Replies: 2
    Last Post: 02-01-2011, 08:47 AM
  5. Replies: 9
    Last Post: 12-18-2010, 12:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums