Finding and naming duplicates

[Heather]

Hi All,
not sure if anyone can help with this.

I have a column called VNTR24 which has 10+ digit numbers - these are not
unique and we want to be able to identify people with matching VNTR24 numbers
to cluster them.

In excel I've used the following formula: =COUNTIF(A:A,A2)>1
to return in the next column a TRUE or FALSE based on whether two records
are matched on VNTR24 number.

I need to do the same in Access 2007, returning either a "TRUE" or "FALSE"
if the records are clustered/matched on this VNTR24 or not in an UPDATE
query!?

Can anyone help please?
NB: I use design view mostly as am just learning SQL.
Many thanks in advance for you advice.
Heather

 

[Allen Browne]

Heather, can I suggest that storing this in a yes/no field (other than in a
temporary table) is not a good idea in a database. If more records are added
or some are deleted, the data in this yes/no field is actually wrong. You
really don't want to design a database so that it will go wrong!

(Technically, the rules of data normalization mean you must not store
dependent data like that.)

Assuming that the records are sorted by a unique field (e.g. an AutoNumber
primary key), you could use a subquery to determine whether there are any
lower ID values for the VNTR24. If not, you want FALSE; if so, it's a
duplicate so you want TRUE. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

There may be other simpler solutions, e.g. if you create a report, the text
boxes have a Hide Duplicates property to suppress the value when it's a
duplicate.

 

[Heather]

Hi Allen,

thanks for the link which is helpful in general, but I couldn't apply any of
the principals to my specific query. I'm still learning SQL and query design
so have a long way to go!

If you have any ideas for a specific Q to address this it would be much
appreciated.
Thanks
Heather

 

[Allen Browne]

Save a query like this:
SELECT VNTR24,
Count(ID) AS CountOfID,
Min(ID) AS MinOfID
FROM Table1
GROUP BY VNTR24;

Save. Now create another query using this one and your main table as input
'tables.' Drag your main table's ID, and drop onto CountOfID in the upper
pane of table design. Access creates a line joining the 2 tables.
Double-click this join line. Access pops up a dialog with 3 options. Choose
the one that says:
All records from Table1, and any matches from Query1.

Now type an expression like this into a fresh column in the Field row:
IsDuplicate: (MinOfID Is Null)

Your query will show True (-1) for duplicates, and False (0) for the first
row (assuming you sort by the ID.)

 

[Heather]

Hi Allen,
thanks for that. I didn't manage to get the subQ to work, the first Q did,
but not the second for some reason.

But, trying the second Q in isolation in a new Q did work, so problem solved.

Thanks for your help
Heather