Query to extract number of duplicates

This query is driving me insane.
I need to extract the number of rows in tblUsers where the first 3
characters of "fldSurname" are the same and they where born "fldDOB" on the
same year and month.
Can anyone help ?Poppy,
Try this link from MS!
I had to use it the other w to remove 150,000 duplicate rows from a live
database and it worked a treat.
You need to modify the SQL slightly to accomodate your data, but the logic
is good and should help you out.
Thanks Microsoft! :)
Immy
"poppy" <poppy@.discussions.microsoft.com> wrote in message
news:F7BE4539-D96B-4FDF-AD48-188649EC4CF9@.microsoft.com...
> This query is driving me insane.
> I need to extract the number of rows in tblUsers where the first 3
> characters of "fldSurname" are the same and they where born "fldDOB" on
> the
> same year and month.
> Can anyone help ?|||if all you need is the count:
select left(fldSurname,3) as surname_start, fldDOB, count(*) as rows
from tblUsers
group by left(fldSurname,3), fldDOB
poppy wrote:
> This query is driving me insane.
> I need to extract the number of rows in tblUsers where the first 3
> characters of "fldSurname" are the same and they where born "fldDOB" on th
e
> same year and month.
> Can anyone help ?|||What link from MS '
"Immy" wrote:

> Poppy,
> Try this link from MS!
> I had to use it the other w to remove 150,000 duplicate rows from a liv
e
> database and it worked a treat.
> You need to modify the SQL slightly to accomodate your data, but the logi
c
> is good and should help you out.
> Thanks Microsoft! :)
> Immy
> "poppy" <poppy@.discussions.microsoft.com> wrote in message
> news:F7BE4539-D96B-4FDF-AD48-188649EC4CF9@.microsoft.com...
>
>|||DOH!!!! God damn CTRL+C! :)
Here it is...
Just note that this will help to remove the records also...
If you want to just see how many you have then a simple count as suggested
by Trey will show the results.
http://support.microsoft.com/defaul...kb;en-us;139444
Regards
Immy
"poppy" <poppy@.discussions.microsoft.com> wrote in message
news:AFCD89C6-2862-4304-B554-0581E085E78C@.microsoft.com...
> What link from MS '
> "Immy" wrote:
>