mark duplicates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maryj - 18 Feb 2008 13:49 GMT

We are in the process of trying to clean up a mess in our database. I have
used the find duplicates wizard to identify dups based on FieldA. Is there a
way that I can assign some type of an identifier to indicate which duplicates
match each other? For example, record 3 and record 10 are duplicates. I would
like a field called MatchID which would assign both of these 2 records the
same number. Any ideas??? Thanks!!

Signature

maryj

Reply to this Message

John Spencer - 18 Feb 2008 16:46 GMT

Does the table have a primary key? If so, you might be able to grab the
primary key (max or min) and use that to flag the matches

Assuming that FieldA is a text field, the SQL might look something like the
following.

SELECT DMax("PrimaryKeyField","TheTable","FieldA =""" & FieldA & """") as
MatchGrp
, *
FROM TheTable as A
WHERE FieldA IN
(SELECT FIELDA FROM TheTable as B
GROUP BY FIELDA
HAVING COUNT(FieldA) >1)
ORDER BY FieldA

If FieldA is not a text field, but is a number field then change to
DMax("PrimaryKeyField","TheTable","FieldA =" & FieldA) as MatchGrp
And if Field A is a date, then try

DMax("PrimaryKeyField","TheTable","FieldA =#" & FieldA & "#") as MatchGrp

Although that may not work well if you are not using the US format of Month
Day Year. In that case you might try

DMax("PrimaryKeyField","TheTable","FieldA =#" & FORMAT(FieldA,"yyyy-mm-dd")
& "#") as MatchGrp

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> We are in the process of trying to clean up a mess in our database. I have
> used the find duplicates wizard to identify dups based on FieldA. Is there
[quoted text clipped - 5 lines]
> like a field called MatchID which would assign both of these 2 records the
> same number. Any ideas??? Thanks!!

Reply to this Message

maryj - 18 Feb 2008 17:40 GMT

John,

The table does have a PK, but it is an SAP# and the duplicate accounts have
different SAP numbers.

The field I am matching on is a text field and I have a query that lists the
potential duplicates. What I want to do now is to add an additional field
that will assign the duplicates a new identical value. Looking at the SQL you
suggested, that seems to only help in generating the list of duplicates - or
did I miss something?

Signature

maryj

> Does the table have a primary key? If so, you might be able to grab the
> primary key (max or min) and use that to flag the matches
[quoted text clipped - 33 lines]
> > like a field called MatchID which would assign both of these 2 records the
> > same number. Any ideas??? Thanks!!

Reply to this Message

John Spencer - 18 Feb 2008 19:27 GMT

Try it. It should give you the Max SAP # for the group of records where the
text value of your field is identical.. It will probably be S L O W
especially if you have a large selection of duplicates.

By the way, it often helps if you post the SQL view of the query you have
already. Sometimes we can take that query and modify for you to try.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> John,
>
[quoted text clipped - 55 lines]
>> > the
>> > same number. Any ideas??? Thanks!!

Reply to this Message

maryj - 18 Feb 2008 20:09 GMT

John,

Stupid question here :)

For this part: SELECT DMax("PrimaryKeyField","TheTable","FieldA =""" &
FieldA & """")

Do I need the quotes? I'm assuming I replace "PrimaryKeyField" with my PK
field and "TheTable" with my table name.

Here's what I have:
SELECT DMax(SAP_MASTNR,DupsOfSingleSAPMake,"Address =""" &Address & """") as
MatchGrp
, *
FROM DupsOfSingleSAPMake as A
WHERE Address IN
(SELECT Address FROM DupsOfSingleSAPMake as B
GROUP BY Address
HAVING COUNT(Address) >1)
ORDER BY Address

but I get a parameter box with the name of my table:DupsOfSingleSAPMake.

Signature

maryj

> Try it. It should give you the Max SAP # for the group of records where the
> text value of your field is identical.. It will probably be S L O W
[quoted text clipped - 69 lines]
> >> > the
> >> > same number. Any ideas??? Thanks!!

Reply to this Message

John Spencer - 18 Feb 2008 20:54 GMT

Yes, you need the quotes. DMax expects strings. First argument is the name
of the field, second argument is the name of the table, third argument is a
where clause without the where.

DMax("SAP_MASTNR","DupsOfSingleSAPMake","Address =""" &Address & """") as
MatchGrp

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> John,
>
[quoted text clipped - 105 lines]
>> >> > the
>> >> > same number. Any ideas??? Thanks!!

Reply to this Message

maryj - 18 Feb 2008 21:23 GMT

Thanks John!
That should work.

Signature

maryj

> Yes, you need the quotes. DMax expects strings. First argument is the name
> of the field, second argument is the name of the table, third argument is a
[quoted text clipped - 112 lines]
> >> >> > the
> >> >> > same number. Any ideas??? Thanks!!

Reply to this Message