[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to create a duplicate finder Application

Posted on 2016-09-08
9
Medium Priority
?
139 Views
Last Modified: 2016-09-25
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
0
Comment
Question by:Nirvana
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 18

Expert Comment

by:xtermie
ID: 41789155
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?
0
 

Author Comment

by:Nirvana
ID: 41789300
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
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41789667
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.
1
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Nirvana
ID: 41790057
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
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41790299
Where are you storing your data, Access?
1
 

Author Comment

by:Nirvana
ID: 41790636
Excel. I have my data in excel and then will be uploaded or imported to access
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 41808484
Nirvana,

Are you still working on this?

If so, the first step is to create a process which will link a file you select (ideally it would have the same file name and path each day). into your database.  The code might look something like:

Private sub cmd_Link_Excel_Click

     docmd.transferspreadsheet acLink, acSpreadsheetTypeExcel12, "ExcelLinked", _
                  "C:\yourpath\yourfilename.xlsx", true, "Sheet1$"

End Sub

This would link "Sheet1" (dont for get to add the $ after the worksheet name) from your file into your Access database.  You could add some code before that to select the file manually if you wanted to,  to do that, search on "vba file dialog" here in EE for code sample.
1
 
LVL 46

Expert Comment

by:aikimark
ID: 41808521
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.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question