Solved

Access - Remove Duplicates on Different Rows

Posted on 2011-03-09
6
258 Views
Last Modified: 2012-05-11
Hi,

In the attached file, I am attempting to create two separate tables from the source table:

1. Duplicate information - but on different rows (Green Rows)
2, Same information (Pink Rows)

In 1., please ignore that the field sequence (i.e. 1 or 2) exists, and examine only the information in the fields. In this case, the information is the same, only that the information changes from the first part of the row to the second part of the row and vice versa in rows 1 and 4.

In rows 2. and 3., please note that the information contained is not the same.

Thank you.


Duplicates---Different-Rows.xlsx
0
Comment
Question by:tahirih
  • 4
  • 2
6 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35087799
That's an Excel spreadsheet. What's the relationship with Access? Are you moving that data into Access? Or is this just an illustration of your data in Access

If so, the simplest way to manage that is to move the data into a Temporary table and then use standard SQL to determine duplicates. I'm not sure how you would define "duplicates" for this data, but if you're looking for matches against ALL data,  you'd have to do this:

1) Build a table hosting ALL data. Make sure to include an AutoNumber Primary key field.
2) Build a recordset loop that will examine all data on a record-by-record basis:

Dim rst As DAO.Recordset
Dim rstMatch as DAO.Recordset

Set rst = DAO.OpenRecordset("SELECT * FROM YourTable")

Do Until rst.EOF
  Set rstMatch = "SELECT * FROM YourTable WHERE Field1=" & rst("Field1") & " AND Field2=" & rst("Field2") & " AND YourPrimaryKeyField <> " & rst("YourPrimaryKeyField"))
  If (rstMatch.EOF and rstMatch.BOF) Then
    '/there was no match
  Else
    '/there was a match
  End If
 
  rst.MoveNext
Loop
 
You would obviously have to modify the SQL statements to match your own tables and fields. Also, if the fields are Text values, you must enclose them in single quotes (see Field1 below) and if they're Dates, you'll have to use hash marks (see Field2 below):

Set rstMatch = "SELECT * FROM YourTable WHERE Field1='" & rst("Field1") & "' AND Field2=#" & rst("Field2") & "# AND YourPrimaryKeyField <> " & rst("YourPrimaryKeyField"))


0
 

Author Comment

by:tahirih
ID: 35088461
Thank you LSM - I also thought of another way - I will attempt both.

I appreciate your patience as I work on this.
0
 

Author Comment

by:tahirih
ID: 35089572
I am not having any luck,

Please find the original table attached. Columns A and E are the first hierarchy, Columns B and F are the second hierarchy. We want to find rows where the values in C and F are different, based on these hierarchies.

That is, A, B, and C are one table

D, E, and F are from the second source table. Are the values in F(D and E) reclassified in different hierarchies based on the values in C (A and B).

Thank you


Duplicates---Different-Rows-2.xlsx
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 84
ID: 35094137
You're still posting an Excel file - are you doing this in Excel?

Have you imported this to Access? If so, can you upload that file and indicate what you've tried so far.

How would you compare (A, B, C) with the data in (D, E, F)? In other words, what columns in (A, B, C) would I attempt to "match" in the columns (D, E, F)? For example, are you trying to match the values of (A, B) with the value of (D, E), and then see if the values of C and F are identical?

F and C don't have any similarties, at least as far as I can see. B and F seem to have similarties.
0
 

Author Comment

by:tahirih
ID: 35096036
I appreciate your time LSM, but at this stage, I am going to break the table into smaller tables and analyze.

Thank you very much.
0
 

Author Closing Comment

by:tahirih
ID: 35096043
Thank you.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now