Display records based on count value of one field

[Guest]

Hi

I have a SQL query.....but I've posted here cos I'm using SQL in a VB.net
app to interrogate records in an Access table. I apologise in advance if I've
posted in the wrong forum and would welcome a pointer to the correct forum to
use....

I have a table containing records that include a name field. A particular
name can occur more than once in the table. I want to be able to view the
full record where the field name contains a value that occurs more than a
given number of times in the table (e.g. => 3)

So if my table contains as follows

RecordID Points Name
1 10 Alan
2 5 Joe
3 10 Joe
4 6 Henry
5 6 Alan
7 10 Joe

I want the query to show the following result

RecordID Points Name
2 5 Joe
3 10 Joe
7 10 Joe

.....and if there were more than records for Alan then they too would
display....
.....my table also contains considerably more fields than the sample I've
given and I need to be able to see the full record with all the fields

I cannot get my head round the syntax of the query despite the use of group
by clause. all that gives me is either a distinct count for the name field or
a count of the name field

Would appreciate any help you can offer.

Regards

Michael Bond

 

[Wolfgang Kais]

Hello Michael.

I have a SQL query.....but I've posted here cos I'm using SQL in a
VB.net app to interrogate records in an Access table. I apologise
in advance if I've posted in the wrong forum and would welcome a
pointer to the correct forum to use....

I guess the forum is ok. Usig the "find duplicates wizard" in Access
you can easily build a query that has a common syntax.

I have a table containing records that include a name field. A
particular name can occur more than once in the table. I want to
be able to view the full record where the field name contains a
value that occurs more than a given number of times in the table
(e.g. => 3)

So if my table contains as follows

RecordID Points Name
1 10 Alan
2 5 Joe
3 10 Joe
4 6 Henry
5 6 Alan
7 10 Joe

I want the query to show the following result

RecordID Points Name
2 5 Joe
3 10 Joe
7 10 Joe

....and if there were more than records for Alan then they too
would display....
....my table also contains considerably more fields than the
sample I've given and I need to be able to see the full record
with all the fields.
I cannot get my head round the syntax of the query despite the use
of group by clause. all that gives me is either a distinct count
for the name field or a count of the name field
Would appreciate any help you can offer.

The magic word is "subquery". Try this:
Select * From YourTable Where Name In (Select Name From YourTable
As Tmp Group By Name Having Count(*) >= 3 Order By Name)

 

[Guest]

Hi Wolfgang,

Thank you so much. The subquery idea never entered my head. Not had time to
try it yet on my full table but I can see how that structure will deliver
what I need

Appreciate your help

Regards

Michael

 

[Wolfgang Kais]

Hello Michael.

You're wellcome. But I made a little mistake:
In order to view the groups of duplicates together, the "Order By"
clause should be moved outside the brackets of the subquery.

 

[Guest]

Hi Wolfgang

I spotted the need for that change when I ran the query on my full table. As
you would expect it gave me the results I need in the format I wanted

Thanks again

Keep up the good work helping us out when we need the help.

Regards

Michael