Solved

Access - Remove Duplicates on Different Rows

Posted on 2011-03-09
6
261 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
[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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

739 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