Access Query - Repeating fields
Home > IT Answers > Microsoft Windows > Access Query - Repeating fields
Dash1
25 pts.
0
Q:
Access Query - Repeating fields
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
ASKED: Aug 13 2009  5:27 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29760 pts.
0
A:
 RATE THIS ANSWER
+2
Click to Vote:
  •   2
  •  0
  • AddThis Social Bookmark Button
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
Update YourFile
 Set DupNameFld = 'Y'
 where NameFld in
 ( Select NameFld from YourFile 
 Group by NameFld
 Having count(*) > 1 )


and update your file to put 'N' s in it
Update YourFile
 Set DupNameFld = 'N'
 where NameFld NOT in
 ( Select NameFld from YourFile 
 Group by NameFld
 Having count(*) > 1 )
Last Answered: Aug 13 2009  9:18 PM GMT by Carlosdl   29760 pts.
Latest Contributors: Philpl1jb   24520 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29760 pts.  |   Aug 13 2009  7:34PM GMT

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

 

Carlosdl   29760 pts.  |   Aug 13 2009  7:46PM GMT

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 )

 

Dash1   25 pts.  |   Aug 13 2009  9:04PM GMT

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.

 

Carlosdl   29760 pts.  |   Aug 13 2009  9:16PM GMT

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.

 

Philpl1jb   24520 pts.  |   Aug 14 2009  2:24AM GMT

Thanks for the fix Carlos - WHERE not When — mind was somewhere else.

 

Dash1   25 pts.  |   Aug 14 2009  3:34AM GMT

Thanks for all your help. That worked!.

 

SbElectric   1600 pts.  |   Aug 17 2009  3:42AM GMT

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.

 
0