Results 1 to 11 of 11
  1. #1
    user9 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Location
    US
    Posts
    43

    Access Query syntax - append if PK doesn't exist, update if PK exists

    This should be a straightforward request, I'm just struggling with the syntax.



    I have a form (frmUpd) that has functionality to write a record to another table (tblApprUpd) when a Save Record button is clicked. There is a Case Number (CASENUM) field that is a Primary Key/unique identifier. What I need is to write syntax to Append data to tblApprUpd if there is not already a record with that CASENUM in the table, or Update/overwrite the record if it does (i.e. if a previous change was added but I want to update with the latest and greatest).

    i.e. a record CASENUM 1000 has field A updated so it is written to tblApprUpd. Then field B is updated and Saved, I need the most recent record with changed to both fields A and B to overwrite the record with just changes to field A.

    Thank you!

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So, create two queries, and Update Query and an Append Query.
    Run the Update Query if the record already exists, or run the Append Query if it does not.
    If you are doing this via VBA, you can use the DLOOKUP function to check ti see if it exists, and then make the decision which query to run.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Sounds suspiciously like duplication of data. Why is same data in two tables?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    user9 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Location
    US
    Posts
    43
    Joe - thank you, I will try this suggestion.

    June - I have a table several people are working from. When they make changes to records in the table, I'm writing those records to another table to identify what changes are being made. You're correct that this is duplication of records, but necessary given the need to identify and isolate these changes as part of a larger process.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Oh, an audit log. Fairly common topic in forum if you want to search on that. Here is Allen Browne's version http://allenbrowne.com/AppAudit.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    user9 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Location
    US
    Posts
    43

    Re: Access Query syntax - append if PK doesn't exist, update if PK exists

    Quote Originally Posted by June7 View Post
    Oh, an audit log. Fairly common topic in forum if you want to search on that. Here is Allen Browne's version http://allenbrowne.com/AppAudit.html

    Thanks for the link June, this looks very useful. Much of the return on audit log searches I did returned suggestions that were much more complex than what I'm looking to do.

  7. #7
    user9 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Location
    US
    Posts
    43
    Ok I may not be approaching this correctly, but here's what I've got. I have to queries to run On Click (they run as expected individually). Both look at existing values in a table. I want to run Query 1 if the Primary Key exists, else run Query 2 to Append.

    Is there a syntax I can apply here? I've run into errors on an IF statement as it's not recognizing the CASENUM field among other things.
    Below are the table/form/query names:
    Table.Field: tblApprUpd.CASENUM
    Form: [Forms]![frmCurrent]![CASENUM]
    Query 1: DoCmd.OpenQuery "qryUpdate_Approve_Updates"
    Query 2: DoCmd.OpenQuery "qryAppend_Approve_Updates"

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Where's your IF statement?
    I think you can use the DLOOKUP function to try to look up the value to determine if it exists in the other table or not, and then decide which query to run.
    Here is a link on DLOOKUP: http://www.techonthenet.com/access/f...in/dlookup.php

  9. #9
    user9 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Location
    US
    Posts
    43
    Quote Originally Posted by JoeM View Post
    Where's your IF statement?
    I think you can use the DLOOKUP function to try to look up the value to determine if it exists in the other table or not, and then decide which query to run.
    Here is a link on DLOOKUP: http://www.techonthenet.com/access/f...in/dlookup.php

    With a little help from the link and your DLOOKUP suggestion, I created the following:
    If DLookup("CASENUM", "tblApprUpd", "CASENUM = " & Forms![frmCurrent]!CASENUM) Then
    DoCmd.OpenQuery "qryUpdate_Approve_Updates"
    Else
    DoCmd.OpenQuery "qryAppend_Approve_Updates"
    End If

    It appears to work. I am just currently not able to test the Update function as one of the keys is a timestamp, which isn't taking effect on the records in my test database (it's only showing the date so it thinks the existing record is a duplicate).

    Thank you!

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It appears to work. I am just currently not able to test the Update function as one of the keys is a timestamp, which isn't taking effect on the records in my test database (it's only showing the date so it thinks the existing record is a duplicate).
    Not quite clear on what you mean.

    Maybe you can post the SQL code of your Update Query, and a small data example to assist in explaining the problem.

  11. #11
    user9 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2013
    Location
    US
    Posts
    43

    Access Query syntax - append if PK doesn't exist, update if PK exists

    Quote Originally Posted by JoeM View Post
    Not quite clear on what you mean.

    Maybe you can post the SQL code of your Update Query, and a small data example to assist in explaining the problem.
    Resolved - thank you both!

    I just had to change some of the timestamp logic for the unique record updates to take effect on existing tables.

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

Similar Threads

  1. Replies: 19
    Last Post: 08-08-2013, 01:17 PM
  2. Replies: 4
    Last Post: 12-14-2012, 06:33 PM
  3. VBA to create PDF and folder if doesn't exist!
    By crxftw in forum Programming
    Replies: 2
    Last Post: 08-08-2011, 08:53 AM
  4. Append if record doesn't exist
    By Lorlai in forum Queries
    Replies: 1
    Last Post: 06-14-2011, 06:38 PM
  5. Form doesn't exist
    By Back2Basics in forum Access
    Replies: 1
    Last Post: 02-01-2010, 11:39 AM

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