General discussion

Locked

Append Query - Already Done

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

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Append Query - Already Done

by chainsawz In reply to Append Query - Already Do ...

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!

Collapse -

Append Query - Already Done

by MRA2 In reply to Append Query - Already Do ...

Poster rated this answer

Collapse -

Append Query - Already Done

by Bob Sellman In reply to Append Query - Already Do ...

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.

Collapse -

Append Query - Already Done

by MRA2 In reply to Append Query - Already Do ...

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

Alan

Collapse -

Append Query - Already Done

by MRA2 In reply to Append Query - Already Do ...

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.

Collapse -

Append Query - Already Done

by dsa In reply to Append Query - Already Do ...

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

Collapse -

Append Query - Already Done

by MRA2 In reply to Append Query - Already Do ...

GREAT & SIMPLE answer!!!!!!!!!

Collapse -

Append Query - Already Done

by Bob Sellman In reply to Append Query - Already Do ...

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

Collapse -

Append Query - Already Done

by MRA2 In reply to Append Query - Already Do ...

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

Alan

Collapse -

Append Query - Already Done

by MRA2 In reply to Append Query - Already Do ...

This question was closed by the author

Back to Web Development Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums