asked on

how to remove dups from the access

every day I am getting the file which supposed to be uploaded in the database.
it has several dups, you can see from the above.
The common :
1. if the record is completed duplicate (by all fields) - the second one should be deleted
2. sometimes the only difference is the Status-- in this case, I need to delete whatever is not Success

that is what i need to have
TestDB.accdb

aikimark

You really need to add an autonumber column.

A delete query would easily and quickly delete all non-success rows.

aikimark

Since there are no other columns, what does it matter whether you deleted the first occurrence of the dup row or the second?

Roman F

ASKER

this is just the test database, in reality, I have more fields...
what does it matter whether you deleted the first occurrence of the dup row or the second?--> no, it does not matter which one

Roman F

ASKER

my input file does not have auto number field

Roman F

ASKER

the main point :
from the table with 7 records to get the table with only 4 records...

aikimark

can you import the data and include an autonumber field?

aikimark

If you need the error status rows deleted, why does your sample illustrate that the 127,GG,TT,error row should stay?

Roman F

ASKER

how to add the auto number in makeup query?

Roman F

ASKER

never mind, now i have the autonum in the table.
TestDBV1.accdb

Roman F

ASKER

DELETE
TblInput.[RecN],
TblInput.[AccNum],
TblInput.[SourceCode],
TblInput.[DocType],
TblInput.[Status],
FROM TblInput
WHERE (((TblInput.[AccNum]) In (SELECT [AccNum] FROM [TblInput] As Tmp GROUP BY [AccNum],[SourceCode],[DocType],[Status] HAVING Count(*)>1 And [SourceCode] = [TblInput].[SourceCode] And [DocType] = [TblInput].[DocType] And [Status] = [TblInput].[Status])));

Roman F

ASKER

with the existing query it will delete all dups, how to keep every other record (2 out of 4)
TestDBV2.accdb

Roman F

ASKER

Please help!

John Tsioumpris

How about creating a query with your table...aggregate it (Totals) ...group by...and then use this to upload it.

Roman F

ASKER

i am lost now

aikimark

Have you thought about my question about your "error" deletion rule?

PatHartman

1. Add a unique index to the target table that includes all the dup fields EXCEPT the status.
2. Find a way to sort the data so that all the duplicate rows sort together and the "Success" status sorts FIRST. Add a numeric value if you need to by using Choose()

When you run the append query, Access will append the first record of every set and discard all duplicates. You just have to get the sort to put the record you want saved when the status' are different to sort first.

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roman F

ASKER

thank you all!!!