CODEWITHSUNDEEP
sqlms-accesscountms-access-2010where-clause
Peter Elias
Peter Elias

Reputation: 317

select query where count = value

I want to create a query like this

SELECT data.Category, data.Company, data.Email
FROM data
WHERE (((data.Category) Like "*Real estate*") AND ((data.Company) Not Like "*CBRE*" And (data.Company) Not Like "*Ellis*" And (data.Company) Not Like "*Douglas*"));

so it will show all the emails for companies that have records less than 11 inside the table

my issue is with the count function

Upvotes: 1

Views: 150

Answers (1)

Linger
Linger

Reputation: 15058

I do belive you are looking for something like the following:

SELECT d.Category, d.Company, d.Email
FROM data AS d
WHERE d.Category LIKE "*Real estate*"
AND 
  (
    d.Company NOT LIKE "*CBRE*" 
    AND d.Company NOT LIKE "*Ellis*" 
    AND d.Company NOT LIKE "*Douglas*"
  )
GROUP BY d.Category, d.Company, d.Email
HAVING COUNT(*) < 11

Upvotes: 4

Related Questions