Number of Duplicate Entries

[Coz]

Hi,

I'm trying to use access to identify how many times an indiviual appears in my database. I've got a large file which gives me all referrals, each referral comes with two identifiers, one unique to the person, another unique to the referral. This means that the person unique number may be repeated multiple times (if a person has more than one referral) but the referral number would be different each time.

I've run a query to identify the duplicates, i.e. the records where the person unique identifier is repeated. What I am trying to do is work out now how many people have been referred twice, how many three times etc. The original query looks something like this (but on a much bigger scale).

Person Referral
1 A
1 B
1 C
2 D
2 E
3 F
3 G
3 H

What I'm after is a query that will tell me that two people were referred three times (1 and 3) and that one was referred twice (2) and so on.

Any ideas? Is this even possible? I'm on Access 2010 if it makes any difference.

Thanks.

 

[pr2-eugin]

Coz, Welcome to AWF

You need a GroupBy Clause or a TOTALS Query, it will look something like

SELECT tableName.Person, Count(tableName.Person) As NoOfTimesReferred
FROM tableName
GROUP BY tableName.Person;

 

[namliam]

select Person, count(*)
from yourtable
group by person

That what you are after??

 

[CJ_London]

Have you tried using the find duplicates wizard?

 

[Coz]

Thank you both so much for your responses. The GroupBy has let me get the number of time each person has been referred, I can from there get to how many twice etc using Excel, where I can considerably more at home!

Thanks again

Coz

 

[pr2-eugin]

If you have used my Query you will get the result like,

Person  |  NoOfTimesReferred
--------+------------------------
1       |     3
2       |     2
3       |     3

IMVHO, You would not need Excel ! Good luck.

 

[namliam]

You can make a query on a query

Select NoOftimesReferred, count(*)
from PR2'sQueryName
group by NoOftimesReferred