Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access - Remove Duplicates on Different Rows

Posted on 2011-03-09
6
Medium Priority
?
268 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

963 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