Append Query to Table Issues

[Pestomania]

Hello,

I have 5 Fields that append to a table. All can repeat but I only want one unique record of each type. When I do the append, it just keeps adding duplicates because I can't set a primary key. Any ideas?

Fields:
Order Operation ID Description Complete

Bear in mind this append will be managing thousands of "Orders".

Order can repeat because you can have multiple operations per order. All of these options can repeat.

But whenever I have them all append, I want it to keep only one unique row. I'll try to show below using three Fields

Order 1 001000 Cut
Order 1 002000 Slice
Order 1 003000 Break

Order 1 001500 Tear
Order 1 002500 Cut
Order 1 003000 Break


As you can see (hopefully). All of these operations belong to Order 1. But the 2 "cuts" are considered unique. But the 2 "breaks" are considered duplicate.
Therefore the anticipated result would be:

Order 1 001000 Cut
Order 1 001500 Tear
Order 1 002000 Slice
Order 1 003000 Break
Order 1 002500 Cut

I hope this makes sense!!

 

[Joe4]

Do an unmatched query between the two tables, joining the table on EVERY field that you want to be considered in the duplicate determination (whether that is 3 fields or 5 fields).
That will return the new "unique" records.
Then, just change the query into an Append Query and add them into your other table.

 

[Norie]

The 2 'Cut' records have different descriptions, so they are unique.

What would you want to have in the description field for the 'Cut' records?

 

[xenou]

Mostly this depends on how you insert these things (one at at time, all at once, from a form, from external data, from user input?).

Anyway, one option is to make sure it doesn't already exist:

SQL PSEUDOCODE (not checked for syntax):

insert into Table(Field1, Field2, Field3)
Values
    (Value1, Value2, Value3) 
where not exists (select * from Table where Field1 = Value1 and Field2 = Value2 and Field3 = Value3)

 

[Pestomania]

Do an unmatched query between the two tables, joining the table on EVERY field that you want to be considered in the duplicate determination (whether that is 3 fields or 5 fields).
That will return the new "unique" records.
Then, just change the query into an Append Query and add them into your other table.

Alright. I think I understand where you're going with this. I will give this a try and See if it brings the results I'm attempting to accomplish.

 

[Pestomania]

Yes. The Cut records are different. That information is pulled from a separate database and cannot be changed.

 

[Pestomania]

What happens is an employee inserts the Order # into a form which prompts the query to pull from a separate database and append it to this table. What I'm having issues with, if they input the order twice (today and tomorrow for example) it appends twice.

Honestly, if I knew the code well enough Id try to write:

If Order 1 is in Table 1 then
Return 'All other fields'
Else
Append Query


May be a better way to do all of that than what I am trying.

The second problem lives that there is a "complete?" Field that the employee selects and I want the next time they scan that order to be able to see that it is already complete. I do this all of the time but the append overrides it.

I hope that helps some!!