mark duplicates

[maryj]

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!!

 

[John Spencer]

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

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

 

[maryj]

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?

 

[John Spencer]

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

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

 

[maryj]

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 tableupsOfSingleSAPMake.

 

[John Spencer]

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


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

 

[maryj]

Thanks John!
That should work.
--
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
where clause without the where.

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


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