Access Query – Repeating fields
I have a file of 70K names. Some are dups. I want to create a indicator in Access to flag when the name is repeated indicate 'Y' else 'N'. Any ideas on how to create a query to do this
Software/Hardware used:
Microsoft Access 2003
Software/Hardware used:
Microsoft Access 2003
ASKED:
August 13, 2009 5:27 PM
UPDATED:
August 17, 2009 3:42 AM
Answer Wiki:
This would give you a list of repeating names
Select NameFld from YourFile
Group by NameFld
Having count(*) > 1
So you can update your file to put 'Y' s in it
<pre>Update YourFile
Set DupNameFld = 'Y'
where NameFld in
( Select NameFld from YourFile
Group by NameFld
Having count(*) > 1 )</pre>
and update your file to put 'N' s in it
<pre>Update YourFile
Set DupNameFld = 'N'
where NameFld NOT in
( Select NameFld from YourFile
Group by NameFld
Having count(*) > 1 )</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
You could avoid the subselect in the second update, assuming that DupNameFld is null for all records at the beginning…
Update YourFile
Set DupNameFld = ‘N’
when DupNameFld is null
Or you could update all records to ‘N’ first, and then update the duplicated to ‘S’.
Thanks for answering my question. I do have a follow up, I’m getting a syntax error with the code, so
I think I’m typing it wrong. To help clarify, the fieldname is ‘phyname’ and the file name is ‘HSD’.
I’m getting an error on the when, but I don’t know why. Thank you.
That was a typo, it is not WHEN, but WHERE.
I modified the original answer from Philpl1jb.
Please change the field and table names as needed.
Thanks for the fix Carlos – WHERE not When — mind was somewhere else.
Thanks for all your help. That worked!.
All nice suggestions and comments.
Just wanted to mention that Access 2007 provides a Wizard (Query) to display Duplicate records. One can select the field for duplicate (Name in this case) along with other associated fields. This may be helpful to ascertain if the record is genuinely duplicate.