Select records which occur for a certain number of

# Thread: Select records which occur for a certain number of

1. Senior Member
Join Date
Dec 1969
Posts
106

## Select records which occur for a certain number of

I have a table with records of names of visitors and the respective dates of their visits, like this<BR><BR>John 20040224<BR>Mary 20040228<BR>Peter 20040302<BR>David 20040312<BR>John 20040315<BR>Linda 20040318<BR>Peter 20040320<BR>John 20040330<BR><BR>I would like to form a recordset of those records which occur more than two times in the table. For example for the table above, the recordset would consists of John and Peter. <BR><BR>Can anymore here suggest a simple way of achieving this?<BR><BR>Thank you!

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## I think this works...

SELECT visitorName, Count(visitorName) As visitCount<BR>FROM visitors<BR>GROUP BY visitorName<BR>HAVING Count(visitorName) &#062;= 2<BR><BR>Give that a try.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
106

## RE: I think this works...

Hi Bill,<BR><BR>It works!<BR><BR>Thanks a lot!

4. Senior Member
Join Date
Dec 1969
Posts
106

## Bill, one further question on this

Your method worked very well.<BR><BR>Now I would like to sort the recordset in descending order of their occurence frequency. For example, in my sample table below, I want John to be in front of Peter because there are 3 records for John and 2 for Peter.<BR><BR>John 20040224<BR>Mary 20040228<BR>Peter 20040302<BR>David 20040312<BR>John 20040315<BR>Linda 20040318<BR>Peter 20040320<BR>John 20040330<BR><BR>Is there a simple way to do it?<BR><BR>Thanks.

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Oh sure...easy...

SELECT visitorName, Count(visitorName) As visitCount <BR>FROM visitors <BR>GROUP BY visitorName <BR>HAVING Count(visitorName) &#062;= 2 <BR>ORDER BY visitCount DESC<BR><BR>That may not work in Access, because it doesn&#039;t like to use "AS" field names in the ORDER BY. If it doesn&#039;t work, just use<BR><BR> ORDER BY 2 DESC<BR><BR>because "VisitCount" is the 2nd item in the SELECT list.<BR><BR>And you can also do<BR> ORDER BY Count(VisitorName) DESC<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
106

## RE: Oh sure...easy...

Hi Bill,<BR><BR>Before I asked, I had tried "ORDER BY visitCount DESC". Sure enough I didn&#039;t work. Then I asked<BR><BR>It&#039;s great that you provided the alternative solutions.<BR><BR>By the way, what is "AS" field names?<BR><BR>Thanks a lot, really!<BR><BR>Now I can hand over the application to my colleague.<BR>

7. Senior Member
Join Date
Dec 1969
Posts
106

## RE: Oh sure...easy...

It&#039;s OK, Bill. I could now see what "AS" field names is. It is the field name after "AS".<BR><BR>Thanks.<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•