Solved

Access - Remove Duplicates on Different Rows

Posted on 2011-03-09
6
259 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

831 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