Count

[Warren]

I have a RealtorOffice table (pk companyID) and associated IndivRealtor table
(fk CompanyID) that have field RealtorName & field RealtorTitle among other
fields.

I'm trying to find the number of individual realtors per office that have a
title other than "Broker Associate". Thanks in advance.

Here's what I have so far but it is not working...

SELECT count(*)
FROM Contact LEFT JOIN Company ON [Company].[CompanyID]=[Contact].[CompanyID]
WHERE [Contact].[Title]<>"Broker Associate"

 

[Chris Anderson [MVP-VB]]

I have a RealtorOffice table (pk companyID) and associated IndivRealtor table
(fk CompanyID) that have field RealtorName & field RealtorTitle among other
fields.

I'm trying to find the number of individual realtors per office that have a
title other than "Broker Associate". Thanks in advance.

Here's what I have so far but it is not working...

SELECT count(*)
FROM Contact LEFT JOIN Company ON [Company].[CompanyID]=[Contact].[CompanyID]
WHERE [Contact].[Title]<>"Broker Associate"

You need another field, preferably one in the select, and add a GROUP BY...
Right now it's just getting a total count of all contacts that are not
Broker Associate... if you want it broken down by company, then
something like this is needed:

SELECT [Company].[CompanyName], COUNT(*)
FROM Contact INNER JOIN Company ON Contact.CompanyID = Company.CompanyID
WHERE Contact.Title <> "Broker Associate"
GROUP BY Company.CompanyName

-ca