473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Append Query Duplicates

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?

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Comments ( Comment, [RCR ID], [Contacts ID] )
  2. SELECT [Qry Checklist].Comment, [Qry Checklist].RCR_ID, [Qry Comments].[Contacts ID]
  3. FROM [Qry Comments] INNER JOIN [Qry Checklist] ON [Qry Comments].[RCR ID] = [Qry Checklist].RCR_ID
  4. 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!
Aug 5 '10 #1
5 1613
Steven Kogan
107 Expert 100+
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?
Aug 5 '10 #2
I run an update query

Expand|Select|Wrap|Line Numbers
  1. UPDATE Checklist SET Checklist.MoveCheck = -1
  2. 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.
Aug 5 '10 #3
Steven Kogan
107 Expert 100+
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Qry Checklist].Comment, [Qry Checklist].RCR_ID, [Qry Comments].[Contacts ID]
  2.  
  3. FROM [Qry Comments] INNER JOIN [Qry Checklist] ON [Qry Comments].[RCR ID] = [Qry Checklist].RCR_ID
  4.  
  5. WHERE ((([Qry Checklist].MoveCheck)=0) AND (([Qry Checklist].Pass_Fail)=-1));
  6.  
Any records with MoveCheck set to -1 should not appear.

After running your first query, wouldn't the follow up update query be:
Expand|Select|Wrap|Line Numbers
  1. UPDATE Checklist SET Checklist.MoveCheck = -1
  2. WHERE (((Checklist.MoveCheck)=0) AND ((Checklist.Pass_Fail)=-1));
  3.  
Aug 5 '10 #4
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.
Aug 5 '10 #5
Steven Kogan
107 Expert 100+
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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Qry Comments] INNER JOIN [Qry Checklist] ON [Qry Comments].[RCR ID] = [Qry Checklist].RCR_ID 
  2. SET [Qry Checklist].MoveCheck=-1
  3.  
  4. WHERE ((([Qry Checklist].MoveCheck)=0) AND (([Qry Checklist].Pass_Fail)=-1)); 
  5.  
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?
Aug 5 '10 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: John | last post by:
Hi - I am trying to perform a simple append query, with no luck. I have a table (MktPrices) that has the following fields: BondID, PriceDate, Price. The objective is to allow the user to input a...
1
by: David B | last post by:
I have a form with 2 listboxes side by side. By double clicking a record in the first lb that record appears in the 2nd lb by way of a table. The record id field in the table is set to no...
9
by: JMCN | last post by:
hi- i have inherited an access 97 database that keeps track of the loans. i have been running into referential intergrity problems when i try to append new loans to table. first of all is a...
1
by: David Barger | last post by:
Greetings, It appears that an Append Query I run in Access XP is randomly failing to append a field. I have payroll data being entered into a payroll database. This data is exported daily to...
4
by: robboll | last post by:
When I try to use an append query from an oracle link it takes forever. I am exploring the idea of doing an append action using a pass-through query. If I have an Oracle ODBC connection to...
1
by: jpr | last post by:
Friends, I would like some help with a code that allows me to run an append query only if a specific field is not already stored into another table. Example. I add a new customer to my database...
4
by: pmacdiddie | last post by:
I have an append query that needs to run every time a line item is added to a subform. The append writes to a table that is the source for a pull down box. Problem is that it takes 5 seconds to...
0
by: jon | last post by:
Hi there, I'm brand new to Access and may be trying to do too much too soon, but I wanted to get some expert advice on how the best way to go about what I am trying to accomplish would be. I...
4
by: Scott12345 | last post by:
Hi, here is my situation, I have a DB that tracks machine downtime (30 machines) per day. Several users will update this through the day. I created an append query that creates 30 dummy values and...
4
by: franc sutherland | last post by:
Hello, I am using Access 2003. I am having trouble trapping the "can't append all the records in the append query" error message when appending data to a query from a table which is linked to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.