Re: Display records based on count value of one field

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" wrote:

Hello Michael.

Michael Bond wrote:
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)

--
Regards,
Wolfgang



.

Relevant Pages

  • Re: Whats the Access equivalent for this SQL Server query?
    ... I see that you've posted on Google Groups or some other forum and ... The subquery selects the most recent cancelled and the query will only ...
    (microsoft.public.access.queries)
  • Re: Display records based on count value of one field
    ... I spotted the need for that change when I ran the query on my full table. ... The subquery idea never entered my head. ... pointer to the correct forum to use.... ... you can easily build a query that has a common syntax. ...
    (microsoft.public.access.queries)
  • Re: Jet SQL and Virtual tables/subqueries qiestions
    ... subquery, but it's curious... ... repeated trips through the Query Editor's "graphical" side did ... The Jet Expression Evaluator (what functions & stuff are actually ... within brackets within the query. ...
    (microsoft.public.access.queries)
  • RE: Display most recent comment in report
    ... There are parentheses around the subquery! ... I'm not sure just what you are doing, but the SQL statement is a query; ... FROM Projects INNER JOIN [Communications Log] AS CL1 ... NoteDate and Notes as their ControlSource properties. ...
    (microsoft.public.access.gettingstarted)
  • Re: Mangled, with a difference
    ... My experience with this subquery as a "table" in SQL in ACCESS (where ACCESS ... query will stop working if I create the query, save it, close it (and ACCESS ... I can't say that I've noted that the mangling differs on different PCs, ... inside the square brackets Access added which are themselves inside square ...
    (microsoft.public.access.queries)