Re: Display records based on count value of one field

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

--
Rehards,
Wolfgang

Michael Bond wrote:
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: Inconsistent query results
    ... As explained in my other response to your question, I can't see any pattern ... query or a table, as long as the source query or table contain the exact ... includes the 'DISTINCT' clause so it contains no duplicate records. ... output (no duplicates) regardless of whether or not has duplicates. ...
    (microsoft.public.access.queries)
  • Re: Memo fields in a query
    ... the concatenation in the DLookup or OutingConvert1 in the FROM clause. ... of these aren't right might be cause of duplicates, ... query on the result table to help clean up although not sure how to use it to ...
    (microsoft.public.access.queries)
  • Re: Help With MS Query Sum Distinct
    ... When I run this query: ... FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles ... This includes the duplicates in the Sum totals. ... the GROUP BY clause. ...
    (microsoft.public.excel.programming)
  • Join query to table - no 1-1 relationship
    ... the best way to join a query to another table ... select statement to remove duplicates and through ... >> clause in my query. ... >> an alias in a where clause. ...
    (microsoft.public.sqlserver.programming)
  • Adding Description to Table in vba
    ... Not sure which forum this belongs in: I am creating a table in order to ... PS If I knew how to pass data to Word from a query with a where clause I ...
    (microsoft.public.access.formscoding)