Link to home
Start Free TrialLog in
Avatar of Nirvana
NirvanaFlag for India

asked on

How to create a duplicate finder Application

We have huge data which consists of 50-60 thousand rows and we receive about 600 to 700 rows of information everyday (excel)

What I am looking for is
1: when the daily data is received if i upload the current data to access or the application it should provide the combination of duplicates as mentioned below in different tabs
2: Able to customize the combinations
3: click on delete button of the combination to be able to delete specific rows from the master data

Customer Name (C)
Customer Number (N)
Invoice Amount (A)
Invoice Number (I)
Invoice Date (D)

some of the experts might have already done this, but I have never got an opportunity to work with access
MOCK_DATA.xlsx
Avatar of Anastasia D. Gavanas
Anastasia D. Gavanas
Flag of Greece image

Perhaps, you can use Excel's remove duplicates prior to importing to access
Open your file in Excel
  1. Click on Data tab
  2. In the Data tools, click Remove Duplicates
  3. Select all columns
  4. Click on Remove Duplicates Button

Not sure of what (2) and (3) means.  Can you please explain a bit more with an example?
Avatar of Nirvana

ASKER

Number 2 mean I should be able customize the combinations from the columns available for duplicates

attaching a sample view of the interface that I am looking for
sample.pptx
Generally, when I import from Excel, I start by linking the Excel file to my database.  Then I import the data from the Excel file into a staging table which contains all of the fields I need from Excel, plus other fields that I use for my error checking process.

Then I run a series of queries that make sure that fields are not duplicates (if they are, I use these additional fields to annotate errors.  These errors might be duplicates based on a single field or multiple fields.  They also might be based on a column which requires a value that exists in a lookup field and does not match any of those values.  

I generally display these records in a form for the user to review and correct or mark mark them for import.  Then, once that process is complete, I import the acceptable records into my production table.  I find that it is easier to prevent these duplicates from the start than it is to import into the production table and then have to find them.
Avatar of Nirvana

ASKER

Thanks Dale.I will follow the steps and see the fact is I have never worked in access so it might take a little time. And how can I create a user interface for end user
Where are you storing your data, Access?
Avatar of Nirvana

ASKER

Excel. I have my data in excel and then will be uploaded or imported to access
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The easiest way to eliminate duplicates is to put a unique index on your destination table.  When you append your rows, any duplicates will not be inserted.