Append Query Duplicates

[brandon Gadish]

I am trying to append a table with records from another table. When I run the Append query the first time it works fine, however when I run it a second time it will duplicate the records. I created criteria in the query to prevent this, however it does not appear to be working. Any suggestions?

INSERT INTO Comments ( Comment, [RCR ID], [Contacts ID] )
SELECT [Qry Checklist].Comment, [Qry Checklist].RCR_ID, [Qry Comments].[Contacts ID]
FROM [Qry Comments] INNER JOIN [Qry Checklist] ON [Qry Comments].[RCR ID] = [Qry Checklist].RCR_ID
WHERE ((([Qry Checklist].MoveCheck)=0) AND (([Qry Checklist].Pass_Fail)=-1));

One more thing to note the MoveCheck is changed to -1 once the append query has run. That is my method of trying to prevent duplicates.

Thanks In Advance!

[Steven Kogan]

If MoveCheck equal -1 then it shouldn't get appended...

Perhaps there is something wrong with the method used to change MoveCheck from 0 to -1. How do you get that to happen after the append query is run?

[brandon Gadish]

I run an update query

UPDATE Checklist SET Checklist.MoveCheck = -1
WHERE (((Checklist.RCR_ID)=[forms]![Frm Checklist]![RCR_ID]) AND ((Checklist.Pass_Fail)=-1));

I tested it by manually setting the value of MoveCheck to -1 as well, and it still didnt work.

[Steven Kogan]

If MoveCheck=-1 then it shouldn't get appended, so manually setting MoveCheck to -1 should prevent the record from getting appended. So this is odd.

To see which records will be appended you can run this query:

SELECT [Qry Checklist].Comment, [Qry Checklist].RCR_ID, [Qry Comments].[Contacts ID]

FROM [Qry Comments] INNER JOIN [Qry Checklist] ON [Qry Comments].[RCR ID] = [Qry Checklist].RCR_ID

WHERE ((([Qry Checklist].MoveCheck)=0) AND (([Qry Checklist].Pass_Fail)=-1));

Any records with MoveCheck set to -1 should not appear.

After running your first query, wouldn't the follow up update query be:

UPDATE Checklist SET Checklist.MoveCheck = -1
WHERE (((Checklist.MoveCheck)=0) AND ((Checklist.Pass_Fail)=-1));

[brandon Gadish]

Thanks for the Select query. I should have thought of that, but that was helpful instead of running through the append query each time. Here is what i am seeing.

The initial run works great. If on the initial run of the append query i select 2 checkboxes in Pass_Fail, Then on the next run each additional box I check will add 3 records to the Comments table. if i initially select 3 then there will be 4 records added the second time run for each additional box checked.

here is my example of what gets appended on the second run after selecting 3 checkboxes on the first run.


Comment RCR_ID Contacts ID
This is a test discussion, components for normal process operations, Blow, Joe
This is a test discussion, components for normal process operations, Blow, Joe
This is a test discussion, components for normal process operations, Blow, Joe
This is a test discussion, components for normal process operations, Blow, Joe
Another Test Discussion, components for normal process operations, Blow, Joe
Another Test Discussion, components for normal process operations, Blow, Joe
Another Test Discussion, components for normal process operations, Blow, Joe
Another Test Discussion, components for normal process operations, Blow, Joe

I hope you get the picture the fields in the query are separated by commas.

[Steven Kogan]

It's sounds like there is a problem, but I'm not quite following.

After running the append query, it sounds like you want to set those records so that MoveCheck=-1. Is that correct?

You'd want to run an update query that does something like this:

UPDATE [Qry Comments] INNER JOIN [Qry Checklist] ON [Qry Comments].[RCR ID] = [Qry Checklist].RCR_ID 
SET [Qry Checklist].MoveCheck=-1
  
WHERE ((([Qry Checklist].MoveCheck)=0) AND (([Qry Checklist].Pass_Fail)=-1));

The difference is you'd want to update the value in a table, and the query would need to be updateable.

Once you get the update query properly marking moved records would that solve the problem?