No Duplicate Records in Table from Append/Delete Queries?

[UberZoot]

Ladies & Gents:
I am running a query that calculates actual production quantity against a set order quantity. I am running append & delete queries to separate pending and completed orders into two tables. Does anyone know how to keep the append query from entering duplicate records into the completed orders table? I want to run a macro which runs the first query, then the append query to copy all information to a table for manipulation, then a second append query to pull out the completed orders, and finally, a delete query to erase completed records from the second append query table. Everything is working so far except for the fact that duplicate orders are showing up in the tables. Can I restrict this data based on some type of No Duplicates property over two fields that are directly related (date of order, product ordered)? My main problem is that this series of queries has to be run multiple times throughout the day to maintain accurate production posting against orders required. I know this is long winded. I really appreciate your help.

 

[UberZoot]

I guess the shortest way to say it would be this: How can I delete the "extra" records so that only the originals (unique by a two/three field combo) stay on the tables? Thanks again.

 

[MichaelRed]

To "Append" only new records, use an UPDATE query.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[dhaveedh]

or you can set a PK on the table into which you are appending data so that no duplicates are accepted.

KISS - Keep It Simple Sugar!

 

[GoDawgs]

Michael,

How do you use an update query to append only new records? I've heard that a couple of times, but never have actually figured out what was meant by that. Thanks for the help.

 

[MichaelRed]

Just try it omn a small dummy sample. Read the Docs (re the ubiquitous {F1}, or most any reference to SQL. This is part of the STANDARD SQL. I 'guess' you could say that if the dest recordset (Table) does not include the record(s) referenced in the src Recordset (UPDATE QUERY), it is necessary to 'update' the dest to match?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places