Solved

Access - Remove Duplicates on Different Rows

Posted on 2011-03-09
6
263 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 85

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 85
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

622 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