Query to find users with certain number of transactions

[RegionsRob]

Is there a way to set up a single query for this?:
I want to return only those transactions for users who have more than 5 transactions.
Example:
Trans#[tab]User
1[tab][tab]Bob
2[tab][tab]Billy
3[tab][tab]Billy
4[tab][tab]Billy
5[tab][tab]Billy
6[tab][tab]Billy

The query would return only those records where Billy is the User.

 

[jebry]

Hi!

In the QBE window select User and Trans#. Click on the summation sign in the toolbar and an extra row should appear in the design grid with Group By already filled in. Leave the Group By in the User column and change it to Count in the Trans# column. Then in the Trans#'s criteria box put >5 or >=5 if that is what you really want.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[RegionsRob]

I had tried using Count in the totals row. I don't know what it's doing, but is certainly not counting the number of transactions.
Where I would expect to get 5 records of 5 Billy, I am getting different counts for each line, and there may or may not be the number of lines that are in the original table. In fact, the first 20 lines of the table are records for only 2 users, and neither user was returned when I put the >=5 criterion in the count column.

Can anyone else help?

 

[RegionsRob]

Well, I discovered my problem. I had another field included in my query (I actually have about 10 fields I want in the final result). After looking at the online help, I changed the "group by" to "Where" in the totals row for that column. Now the Count works correctly.

I guess I'm going to have to build 2 queries to handle this: the one described here, using JUST the fields needed to get the count, and another query based on the first query and my main table to pull the records and other fields for the results of the first query.