CODEWITHSUNDEEP
sqlms-accessms-access-2010
dmorgan20
dmorgan20

Reputation: 363

Only show data that is in database more than 5 times

I have a report that works fine apart from my final hurdle.

Table Example, Row A:

John
John
Mike
John
Steve
Steve

I only want my report to show name that are in my table 5 times or more. I'm struggling to work out what criteria I need to set

Any advice?

from comment:

SELECT tblSourceData.Athlete, Max(tblSourceData.Swim) AS MaxOfSwim, 
    Max(tblSourceData.Bike) AS MaxOfBike, Max(tblSourceData.Run) AS MaxOfRun 
FROM tblSourceData 
GROUP BY tblSourceData.Athlete, tblSourceData.Gender 
HAVING (((tblSourceData.Athlete)>="5") AND ((tblSourceData.Gender)="f"));

Upvotes: 0

Views: 35

Answers (1)

Andre
Andre

Reputation: 27644

You need a HAVING clause.

SELECT aName, Count(aName) AS NumName
FROM myTable
GROUP BY aName
HAVING Count(aName)>=5

Edit

you probably want

SELECT tblSourceData.Athlete, Max(tblSourceData.Swim) AS MaxOfSwim, 
    Max(tblSourceData.Bike) AS MaxOfBike, Max(tblSourceData.Run) AS MaxOfRun 
FROM tblSourceData 
WHERE tblSourceData.Gender="f"
GROUP BY tblSourceData.Athlete 
HAVING Count(tblSourceData.Athlete)>=5

Criteria that don't use aggregated columns go into the WHERE clause.

Upvotes: 2

Related Questions