General discussion

  • Creator
    Topic
  • #2346454

    Append Query – Already Done

    Locked

    by mra2 ·

    I have a table (call it Table 1) with names & dates & payments in Access 97 (There is an entry for each month). From another Table Call it Table 2), I have the same names, and the current amount.
    I made an append query which works well taking the current data from Table 2 and posting it to Table 1.
    But I want to make sure that this posting process is only done ONCE for the month. (I always enter the first day of each month as the date).

    Is there some way to test for the presence of the month’s data and then stopping the append before it is done twice???

    Any help would be greatly appreciated!!!

    abramowitza@coned.com

All Comments

  • Author
    Replies
    • #3638635

      Append Query – Already Done

      by chainsawz ·

      In reply to Append Query – Already Done

      I would set the query UniqueRecord property to ‘yes’. This will prevent any records with the same date being appended.
      Just open your append query in design view, and right click to properties, and set the property.

      I hope this helps!

    • #3628967

      Append Query – Already Done

      by bob sellman ·

      In reply to Append Query – Already Done

      To be more sure of avoiding duplicate entries, I would suggest doing the following.

      A simple effective solution is to add a column to Table 2 called “Posted”. Your posting process would be a 2 step situation. First, the query would require thatPosted would be less than the date you are entering. Second, another query would post the new date to the Posted column in Table 2. The easiest way to do this is to create a form with a control where the date is entered, plus a button to click forthe posting to occur (both queries would be run).

      An alternate would be to create a new small table (“LastPosting”)with the Posted date as the only column. This would then be checked when the button is pushed on the form to perform the update. The second query would just update the LastPosting table with the newest date used. If it’s a large database, this second method would use less space, since the date would only be saved once.

      • #3553992

        Append Query – Already Done

        by mra2 ·

        In reply to Append Query – Already Done

        Answer # 3 was a more simple to use… Thanks for your input!

        Alan

    • #3627933

      Append Query – Already Done

      by mra2 ·

      In reply to Append Query – Already Done

      Bob’s answer was great but I missed one thing. I have the query which tells me if the date entered has already been posted. How do I go about telling the application to generate a message “already posted” or run the query. Sounds like an If then but where does it go and how do I create an expression to say “has the Query found anything or not?

      Thanks for all the help. I will close the question out after this reply is addressed.

    • #3554992

      Append Query – Already Done

      by dsa ·

      In reply to Append Query – Already Done

      If you make the append query as function as follows:
      Function AppendPayment(PostDate) as boolean
      Retrieve first record
      If date = PostDate then
      AppendPayment = False
      Msgbox “Already posted”
      Else
      AppendPayment = True Docmd.OpenQuery “qryADDTbl2totbl1”
      Endif
      End Function

    • #3553447

      Append Query – Already Done

      by bob sellman ·

      In reply to Append Query – Already Done

      Since I’m talking about doing this from a form, a good way to do the test is the following:

      When the user clicks on the button to perform the update query, first run something like the following, assuming you use a separate single record table that just contains the last date posted:

      (Assume me.txtNewPostedDate is the control on the form that has the new date for posting, LastPosted is the table and Posted is the column in the LastPosted table with the last date posted.)

      ‘ test to see if already posted
      ‘ because the LastPosted table only contains one record, no selection parameters are needed in the Dlookup funciton

      if me.txtNewPostedDate = DLookup(“Posted”,”LastPosted”) then
      Docmd.OpenQuery(“PostDate”)
      Else
      msgbox “Already posted for this date”
      end if

      You can also expand the testing of the new posting date to further improve the error checking:

      1. Be sure that the first of the month was actually entered as the date. Do this before checking against the previous month’s date.

      2. Also check to be sure that the new posting date entered is not later than the current date:

      if me.txtNewPostedDate > Date() then
      msgbox “You can’t enter a date later than today!”
      Exit sub
      end if

      • #3553994

        Append Query – Already Done

        by mra2 ·

        In reply to Append Query – Already Done

        Answer # 3 was a more simple to use… Thanks for your input!

        Alan

    • #3553990

      Append Query – Already Done

      by mra2 ·

      In reply to Append Query – Already Done

      This question was closed by the author

Viewing 5 reply threads