Append Query to Table Issues

Pestomania

Board Regular
Joined
May 30, 2018
Messages
145
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!!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,690
Office Version
  1. 2013
Platform
  1. Windows
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):
Code:
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

Board Regular
Joined
May 30, 2018
Messages
145

ADVERTISEMENT

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

Board Regular
Joined
May 30, 2018
Messages
145
Yes. The Cut records are different. That information is pulled from a separate database and cannot be changed.
 

Pestomania

Board Regular
Joined
May 30, 2018
Messages
145
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,951
Messages
5,525,833
Members
409,666
Latest member
aquabit

This Week's Hot Topics

Top