Retrive Duplicate Row value and Count

Question

I have table named "Table X" which contains some names and their corresponding age, table may contains same names (i.e) names will occur repeatedly.

Table x:

name            age
a               21
b               37
c               23
a               34
a               21
b               19
b               37 
a               21

...output like the One Below:

name      total   repeat
 a          4        2
 b          3        1
 c          1        0

Now I want to write a query which will return total # of repeated names, and how many times the age repetitions occur in the resulting table. Just like the output table given above.I want to do it with MS Access.

Answer 1

try this

SELECT x.pname, SUM(x.CountOfpage) as Total, SUM(x.CountOfpage-1) as Repeats
FROM (
SELECT TableX.pname, TableX.page, Count(TableX.page) AS CountOfpage
FROM TableX
GROUP BY TableX.pname, TableX.page
) as x
GROUP BY  x.pname