Re: Display records based on count value of one field

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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)