Access Query – Repeating fields

45 pts.
Tags:
Access query
Microsoft Access
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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
    69,160 pointsBadges:
    report
  • carlosdl
    Or you could update all records to 'N' first, and then update the duplicated to 'S'.
    Update YourFile
    Set DupNameFld = 'N'
    
    
    Update YourFile
    Set DupNameFld = 'Y'
    when NameFld in
    ( Select NameFld from YourFile 
    Group by NameFld
    Having count(*) > 1 )
    69,160 pointsBadges:
    report
  • Dash1
    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.
    45 pointsBadges:
    report
  • carlosdl
    That was a typo, it is not WHEN, but WHERE. I modified the original answer from Philpl1jb.
    Update YourFile
    Set DupNameFld = ā€˜Nā€™
    
    Update YourFile
    Set DupNameFld = ā€˜Yā€™
    where NameFld in
    ( Select NameFld from YourFile
    Group by NameFld
    Having count(*) > 1 )
    Please change the field and table names as needed.
    69,160 pointsBadges:
    report
  • philpl1jb
    Thanks for the fix Carlos - WHERE not When -- mind was somewhere else.
    49,830 pointsBadges:
    report
  • Dash1
    Thanks for all your help. That worked!.
    45 pointsBadges:
    report
  • SbElectric
    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.
    2,540 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following