How do I query companies not having members w/certain level of mgm

[Warren]

I have four queries below. 1,2,&3 work properly but 4 is working but not
properly - it is returning the whole table. Appreciate any suggestions.
(There are two tables: Comapny table (pk companyID) and associated Contact
table (fk CompanyID) that have field "Name" & field "Title" among other
fields.) THANKS!!!!


1) ==Query returning companies that have members with a title.
SELECT [Contact].[FirstName]+" "+[Contact].[LastName]+", "+[Contact].[Title]
AS NAME,
[Company].[CompanyName]
FROM Company INNER JOIN Contact ON [Company].[CompanyID]=[Contact].[CompanyID]
WHERE NOT IsNull([Company].[Address]) AND NOT IsNull([Contact].[Title]);

2) ==Query returning companies that have members with no title.
SELECT [Contact].[FirstName]+" "+[Contact].[LastName] AS NAME,
[Company].[CompanyName]
FROM Company INNER JOIN Contact ON [Company].[CompanyID]=[Contact].[CompanyID]
WHERE NOT IsNull([Company].[Address]) AND IsNull([Contact].[Title]);

3) ==Query returning companies that have no members at all.
SELECT "Managing Broker" AS NAME,
[Company].[CompanyName]
FROM Company LEFT JOIN Contact
ON [Company].[CompanyID]=[Contact].[CompanyID]
WHERE [Contact].[LastName] IS NULL;

4) ==THIS ONE IS EXECUTING BUT NOT RETURNING CORRECT RESULTS. Query should
return companies that have members but none with a management type of title.
In this task, “Broker Associate†is the only non-management title.
SELECT "Managing Broker" AS NAME,
[Company].[CompanyName]
FROM Contact INNER JOIN Company ON Contact.CompanyID = Company.CompanyID
WHERE (SELECT COUNT(*) FROM Contact WHERE ([Contact].[Title] <> "Broker
Associate" OR NOT IsNull([Contact].[Title])))>0

 

[Jeanette Cunningham]

Warren,

SELECT [FirstName] & " " & [LastName] AS [Managing Broker],
Company.CompanyName
FROM Company INNER JOIN Contact ON Company.CompanyID = Contact.CompanyID
WHERE ((Not (Contact.Title) Is Null And Not (Contact.Title)<>"broker
associate"));

Jeanette Cunningham

 

[Warren]

ALMOST...It returns all persons with a "Broker Associate" title and I don't
want it to. Let me slightly elborate on my requirements. I want to send
mkting materal to persons (members) within R/E offices that have titles
implying some managment (all titles except "Broker Associate").
SO... 1) Offices with no members in my list (Contacts) gets a peice of
material addressed to the "Managing Broker; 2) In offices that have members
with the appropriate title, each of those members gets the same mktg material
sent to them. In this case, if there are other members that have the "Broker
Associate" title they should get nothing sent to them. 3) Offices that have
only "Broker Associate" members in the contact list get one piece of materal
sent & addressed to the "Managing Broker", not those assciae.
SO... after I playing with your code, I noticed that my #1 below actually
returns all members with the title "Broker Associate" and it is not supposed
to.

I appreciate any other suggestions.
Warren

Warren,

SELECT [FirstName] & " " & [LastName] AS [Managing Broker],
Company.CompanyName
FROM Company INNER JOIN Contact ON Company.CompanyID = Contact.CompanyID
WHERE ((Not (Contact.Title) Is Null And Not (Contact.Title)<>"broker
associate"));

Jeanette Cunningham

I have four queries below. 1,2,&3 work properly but 4 is working but not
properly - it is returning the whole table. Appreciate any suggestions.
(There are two tables: Comapny table (pk companyID) and associated Contact
table (fk CompanyID) that have field "Name" & field "Title" among other
fields.) THANKS!!!!


1) ==Query returning companies that have members with a title.
SELECT [Contact].[FirstName]+" "+[Contact].[LastName]+",
"+[Contact].[Title]
AS NAME,
[Company].[CompanyName]
FROM Company INNER JOIN Contact ON
[Company].[CompanyID]=[Contact].[CompanyID]
WHERE NOT IsNull([Company].[Address]) AND NOT IsNull([Contact].[Title]);

2) ==Query returning companies that have members with no title.
SELECT [Contact].[FirstName]+" "+[Contact].[LastName] AS NAME,
[Company].[CompanyName]
FROM Company INNER JOIN Contact ON
[Company].[CompanyID]=[Contact].[CompanyID]
WHERE NOT IsNull([Company].[Address]) AND IsNull([Contact].[Title]);

3) ==Query returning companies that have no members at all.
SELECT "Managing Broker" AS NAME,
[Company].[CompanyName]
FROM Company LEFT JOIN Contact
ON [Company].[CompanyID]=[Contact].[CompanyID]
WHERE [Contact].[LastName] IS NULL;

4) ==THIS ONE IS EXECUTING BUT NOT RETURNING CORRECT RESULTS. Query
should
return companies that have members but none with a management type of
title.
In this task, "Broker Associate" is the only non-management title.
SELECT "Managing Broker" AS NAME,
[Company].[CompanyName]
FROM Contact INNER JOIN Company ON Contact.CompanyID = Company.CompanyID
WHERE (SELECT COUNT(*) FROM Contact WHERE ([Contact].[Title] <> "Broker
Associate" OR NOT IsNull([Contact].[Title])))>0

 

[Jeanette Cunningham]

Warren,
there are six queries, you need them all. They are interdependent.
Save each query with the name I have given below each query.
qFinal gives the Title, Full Name, Company Name and Company Address

------------
SELECT DISTINCT Contact.CompanyID
FROM Contact
GROUP BY Contact.CompanyID
HAVING (((Count(Contact.CompanyID))=1));
------------ save as qCompanyOneContact

------------
SELECT DISTINCT Contact.CompanyID
FROM Contact
GROUP BY Contact.CompanyID
HAVING (((Count(Contact.CompanyID))>1));
----------- save as qCompanyManyContact

-----------
SELECT qCompanyManyContact.CompanyID
FROM Contact INNER JOIN qCompanyManyContact ON Contact.CompanyID =
qCompanyManyContact.CompanyID
WHERE ((Not (Contact.Title) Is Null And (Contact.Title)<>"broker
associate"))
UNION SELECT DISTINCT Contact.CompanyID
FROM Contact
GROUP BY Contact.CompanyID
HAVING (((Count(Contact.CompanyID))=1));
---------- save as qCompanyOneAndManager


----------
SELECT Company.CompanyID
FROM Company LEFT JOIN qCompanyOneAndManager ON Company.CompanyID =
qCompanyOneAndManager.CompanyID
WHERE (((qCompanyOneAndManager.CompanyID) Is Null));
--------- save as qCompanyManyAssocBelow


---------
SELECT Contact.ContactID, "Managing Broker" AS NewTitle
FROM qCompanyManyAssocBelow INNER JOIN Contact ON
qCompanyManyAssocBelow.CompanyID = Contact.CompanyID
WHERE (((Contact.Title)="broker associate"))
UNION
SELECT Contact.ContactID, Contact.Title As NewTitle
FROM Contact INNER JOIN qCompanyManyContact ON Contact.CompanyID =
qCompanyManyContact.CompanyID
WHERE ((Not (Contact.Title) Is Null And (Contact.Title)<>"broker
associate"))
UNION SELECT Contact.ContactID, IIf(IsNull([title]) Or ([Title]="Broker
Associate"),"Managing Director",[title]) AS NewTitle
FROM Contact INNER JOIN qCompanyOneContact ON Contact.CompanyID =
qCompanyOneContact.CompanyID;
-------- save as qAllTitles


--------
SELECT qAllTitles.NewTitle, [FirstName] & " " & [LastName] AS FullName,
Company.CompanyName, Company.CompanyAddress
FROM (Contact INNER JOIN Company ON Contact.CompanyID = Company.CompanyID)
INNER JOIN qAllTitles ON Contact.ContactID = qAllTitles.ContactID;
------- save as qFinal


Jeanette Cunningham

ALMOST...It returns all persons with a "Broker Associate" title and I
don't
want it to. Let me slightly elborate on my requirements. I want to send
mkting materal to persons (members) within R/E offices that have titles
implying some managment (all titles except "Broker Associate").
SO... 1) Offices with no members in my list (Contacts) gets a peice of
material addressed to the "Managing Broker; 2) In offices that have
members
with the appropriate title, each of those members gets the same mktg
material
sent to them. In this case, if there are other members that have the
"Broker
Associate" title they should get nothing sent to them. 3) Offices that
have
only "Broker Associate" members in the contact list get one piece of
materal
sent & addressed to the "Managing Broker", not those assciae.
SO... after I playing with your code, I noticed that my #1 below actually
returns all members with the title "Broker Associate" and it is not
supposed
to.

I appreciate any other suggestions.
Warren

Warren,

SELECT [FirstName] & " " & [LastName] AS [Managing Broker],
Company.CompanyName
FROM Company INNER JOIN Contact ON Company.CompanyID = Contact.CompanyID
WHERE ((Not (Contact.Title) Is Null And Not (Contact.Title)<>"broker
associate"));

Jeanette Cunningham

I have four queries below. 1,2,&3 work properly but 4 is working but
not
properly - it is returning the whole table. Appreciate any
suggestions.
(There are two tables: Comapny table (pk companyID) and associated
Contact
table (fk CompanyID) that have field "Name" & field "Title" among other
fields.) THANKS!!!!


1) ==Query returning companies that have members with a title.
SELECT [Contact].[FirstName]+" "+[Contact].[LastName]+",
"+[Contact].[Title]
AS NAME,
[Company].[CompanyName]
FROM Company INNER JOIN Contact ON
[Company].[CompanyID]=[Contact].[CompanyID]
WHERE NOT IsNull([Company].[Address]) AND NOT
IsNull([Contact].[Title]);

2) ==Query returning companies that have members with no title.
SELECT [Contact].[FirstName]+" "+[Contact].[LastName] AS NAME,
[Company].[CompanyName]
FROM Company INNER JOIN Contact ON
[Company].[CompanyID]=[Contact].[CompanyID]
WHERE NOT IsNull([Company].[Address]) AND IsNull([Contact].[Title]);

3) ==Query returning companies that have no members at all.
SELECT "Managing Broker" AS NAME,
[Company].[CompanyName]
FROM Company LEFT JOIN Contact
ON [Company].[CompanyID]=[Contact].[CompanyID]
WHERE [Contact].[LastName] IS NULL;

4) ==THIS ONE IS EXECUTING BUT NOT RETURNING CORRECT RESULTS. Query
should
return companies that have members but none with a management type of
title.
In this task, "Broker Associate" is the only non-management title.
SELECT "Managing Broker" AS NAME,
[Company].[CompanyName]
FROM Contact INNER JOIN Company ON Contact.CompanyID =
Company.CompanyID
WHERE (SELECT COUNT(*) FROM Contact WHERE ([Contact].[Title] <> "Broker
Associate" OR NOT IsNull([Contact].[Title])))>0

 

[Warren]

I think it works but I have one clarification...I don't want to send any mktg
material to any "Broker Associates". Therefore to accomplish that all I
would need to do is eliminate the SELECT after the last union in qAllTitles,
correct?
-Warren

Warren,
there are six queries, you need them all. They are interdependent.
Save each query with the name I have given below each query.
qFinal gives the Title, Full Name, Company Name and Company Address

------------
SELECT DISTINCT Contact.CompanyID
FROM Contact
GROUP BY Contact.CompanyID
HAVING (((Count(Contact.CompanyID))=1));
------------ save as qCompanyOneContact

------------
SELECT DISTINCT Contact.CompanyID
FROM Contact
GROUP BY Contact.CompanyID
HAVING (((Count(Contact.CompanyID))>1));
----------- save as qCompanyManyContact

-----------
SELECT qCompanyManyContact.CompanyID
FROM Contact INNER JOIN qCompanyManyContact ON Contact.CompanyID =
qCompanyManyContact.CompanyID
WHERE ((Not (Contact.Title) Is Null And (Contact.Title)<>"broker
associate"))
UNION SELECT DISTINCT Contact.CompanyID
FROM Contact
GROUP BY Contact.CompanyID
HAVING (((Count(Contact.CompanyID))=1));
---------- save as qCompanyOneAndManager


----------
SELECT Company.CompanyID
FROM Company LEFT JOIN qCompanyOneAndManager ON Company.CompanyID =
qCompanyOneAndManager.CompanyID
WHERE (((qCompanyOneAndManager.CompanyID) Is Null));
--------- save as qCompanyManyAssocBelow


---------
SELECT Contact.ContactID, "Managing Broker" AS NewTitle
FROM qCompanyManyAssocBelow INNER JOIN Contact ON
qCompanyManyAssocBelow.CompanyID = Contact.CompanyID
WHERE (((Contact.Title)="broker associate"))
UNION
SELECT Contact.ContactID, Contact.Title As NewTitle
FROM Contact INNER JOIN qCompanyManyContact ON Contact.CompanyID =
qCompanyManyContact.CompanyID
WHERE ((Not (Contact.Title) Is Null And (Contact.Title)<>"broker
associate"))
UNION SELECT Contact.ContactID, IIf(IsNull([title]) Or ([Title]="Broker
Associate"),"Managing Director",[title]) AS NewTitle
FROM Contact INNER JOIN qCompanyOneContact ON Contact.CompanyID =
qCompanyOneContact.CompanyID;
-------- save as qAllTitles


--------
SELECT qAllTitles.NewTitle, [FirstName] & " " & [LastName] AS FullName,
Company.CompanyName, Company.CompanyAddress
FROM (Contact INNER JOIN Company ON Contact.CompanyID = Company.CompanyID)
INNER JOIN qAllTitles ON Contact.ContactID = qAllTitles.ContactID;
------- save as qFinal


Jeanette Cunningham

ALMOST...It returns all persons with a "Broker Associate" title and I
don't
want it to. Let me slightly elborate on my requirements. I want to send
mkting materal to persons (members) within R/E offices that have titles
implying some managment (all titles except "Broker Associate").
SO... 1) Offices with no members in my list (Contacts) gets a peice of
material addressed to the "Managing Broker; 2) In offices that have
members
with the appropriate title, each of those members gets the same mktg
material
sent to them. In this case, if there are other members that have the
"Broker
Associate" title they should get nothing sent to them. 3) Offices that
have
only "Broker Associate" members in the contact list get one piece of
materal
sent & addressed to the "Managing Broker", not those assciae.
SO... after I playing with your code, I noticed that my #1 below actually
returns all members with the title "Broker Associate" and it is not
supposed
to.

I appreciate any other suggestions.
Warren

Warren,

SELECT [FirstName] & " " & [LastName] AS [Managing Broker],
Company.CompanyName
FROM Company INNER JOIN Contact ON Company.CompanyID = Contact.CompanyID
WHERE ((Not (Contact.Title) Is Null And Not (Contact.Title)<>"broker
associate"));

Jeanette Cunningham

I have four queries below. 1,2,&3 work properly but 4 is working but
not
properly - it is returning the whole table. Appreciate any
suggestions.
(There are two tables: Comapny table (pk companyID) and associated
Contact
table (fk CompanyID) that have field "Name" & field "Title" among other
fields.) THANKS!!!!


1) ==Query returning companies that have members with a title.
SELECT [Contact].[FirstName]+" "+[Contact].[LastName]+",
"+[Contact].[Title]
AS NAME,
[Company].[CompanyName]
FROM Company INNER JOIN Contact ON
[Company].[CompanyID]=[Contact].[CompanyID]
WHERE NOT IsNull([Company].[Address]) AND NOT
IsNull([Contact].[Title]);

2) ==Query returning companies that have members with no title.
SELECT [Contact].[FirstName]+" "+[Contact].[LastName] AS NAME,
[Company].[CompanyName]
FROM Company INNER JOIN Contact ON
[Company].[CompanyID]=[Contact].[CompanyID]
WHERE NOT IsNull([Company].[Address]) AND IsNull([Contact].[Title]);

3) ==Query returning companies that have no members at all.
SELECT "Managing Broker" AS NAME,
[Company].[CompanyName]
FROM Company LEFT JOIN Contact
ON [Company].[CompanyID]=[Contact].[CompanyID]
WHERE [Contact].[LastName] IS NULL;

4) ==THIS ONE IS EXECUTING BUT NOT RETURNING CORRECT RESULTS. Query
should
return companies that have members but none with a management type of
title.
In this task, "Broker Associate" is the only non-management title.
SELECT "Managing Broker" AS NAME,
[Company].[CompanyName]
FROM Contact INNER JOIN Company ON Contact.CompanyID =
Company.CompanyID
WHERE (SELECT COUNT(*) FROM Contact WHERE ([Contact].[Title] <> "Broker
Associate" OR NOT IsNull([Contact].[Title])))>0

 

[Warren]

Please ignor my first reply. I see how they work but it is not getting the
results I want. My apologies for not explaining the scenario better and
thank-you for your help. Here's hopefully a better explanation of my
criteria.

REAL ESTATE OFFICE MAILING LIST CRITERIA
1) Offices with no members yet in my list (Contacts) get on the mailing list
and are addressed to: "The Managing Brokerâ€.
2) Offices with only “Broker Associate†members or members with no title in
my list get on the mailing list and are also addressed to: "The Managing
Brokerâ€. (Note: the office gets put on the mailing list – not the
individual).
3) In Offices that contain members with titles other than Null / blank or
“Broker Associateâ€, those individual members (“Brokerâ€, “Broker/Ownerâ€,
“Managing Brokerâ€, “Presidentâ€, etc., etc.) will be put on the mailing list
with their title. (Note: For these offices, my list may also contain
individuals with “Broker Associate†but they individually don’t get on the
mailing list.)

DEFINITION
Member: is any Realtor or officer within an office. Each member may have a
title with a value of Null or blank (in case I don’t know his/her title),
“Broker Associateâ€, “Brokerâ€, “Broker/Ownerâ€, “Managing Brokerâ€, “Presidentâ€,
etc., etc.

Warren,
there are six queries, you need them all. They are interdependent.
Save each query with the name I have given below each query.
qFinal gives the Title, Full Name, Company Name and Company Address

------------
SELECT DISTINCT Contact.CompanyID
FROM Contact
GROUP BY Contact.CompanyID
HAVING (((Count(Contact.CompanyID))=1));
------------ save as qCompanyOneContact

------------
SELECT DISTINCT Contact.CompanyID
FROM Contact
GROUP BY Contact.CompanyID
HAVING (((Count(Contact.CompanyID))>1));
----------- save as qCompanyManyContact

-----------
SELECT qCompanyManyContact.CompanyID
FROM Contact INNER JOIN qCompanyManyContact ON Contact.CompanyID =
qCompanyManyContact.CompanyID
WHERE ((Not (Contact.Title) Is Null And (Contact.Title)<>"broker
associate"))
UNION SELECT DISTINCT Contact.CompanyID
FROM Contact
GROUP BY Contact.CompanyID
HAVING (((Count(Contact.CompanyID))=1));
---------- save as qCompanyOneAndManager


----------
SELECT Company.CompanyID
FROM Company LEFT JOIN qCompanyOneAndManager ON Company.CompanyID =
qCompanyOneAndManager.CompanyID
WHERE (((qCompanyOneAndManager.CompanyID) Is Null));
--------- save as qCompanyManyAssocBelow


---------
SELECT Contact.ContactID, "Managing Broker" AS NewTitle
FROM qCompanyManyAssocBelow INNER JOIN Contact ON
qCompanyManyAssocBelow.CompanyID = Contact.CompanyID
WHERE (((Contact.Title)="broker associate"))
UNION
SELECT Contact.ContactID, Contact.Title As NewTitle
FROM Contact INNER JOIN qCompanyManyContact ON Contact.CompanyID =
qCompanyManyContact.CompanyID
WHERE ((Not (Contact.Title) Is Null And (Contact.Title)<>"broker
associate"))
UNION SELECT Contact.ContactID, IIf(IsNull([title]) Or ([Title]="Broker
Associate"),"Managing Director",[title]) AS NewTitle
FROM Contact INNER JOIN qCompanyOneContact ON Contact.CompanyID =
qCompanyOneContact.CompanyID;
-------- save as qAllTitles


--------
SELECT qAllTitles.NewTitle, [FirstName] & " " & [LastName] AS FullName,
Company.CompanyName, Company.CompanyAddress
FROM (Contact INNER JOIN Company ON Contact.CompanyID = Company.CompanyID)
INNER JOIN qAllTitles ON Contact.ContactID = qAllTitles.ContactID;
------- save as qFinal


Jeanette Cunningham

ALMOST...It returns all persons with a "Broker Associate" title and I
don't
want it to. Let me slightly elborate on my requirements. I want to send
mkting materal to persons (members) within R/E offices that have titles
implying some managment (all titles except "Broker Associate").
SO... 1) Offices with no members in my list (Contacts) gets a peice of
material addressed to the "Managing Broker; 2) In offices that have
members
with the appropriate title, each of those members gets the same mktg
material
sent to them. In this case, if there are other members that have the
"Broker
Associate" title they should get nothing sent to them. 3) Offices that
have
only "Broker Associate" members in the contact list get one piece of
materal
sent & addressed to the "Managing Broker", not those assciae.
SO... after I playing with your code, I noticed that my #1 below actually
returns all members with the title "Broker Associate" and it is not
supposed
to.

I appreciate any other suggestions.
Warren

Warren,

SELECT [FirstName] & " " & [LastName] AS [Managing Broker],
Company.CompanyName
FROM Company INNER JOIN Contact ON Company.CompanyID = Contact.CompanyID
WHERE ((Not (Contact.Title) Is Null And Not (Contact.Title)<>"broker
associate"));

Jeanette Cunningham

I have four queries below. 1,2,&3 work properly but 4 is working but
not
properly - it is returning the whole table. Appreciate any
suggestions.
(There are two tables: Comapny table (pk companyID) and associated
Contact
table (fk CompanyID) that have field "Name" & field "Title" among other
fields.) THANKS!!!!


1) ==Query returning companies that have members with a title.
SELECT [Contact].[FirstName]+" "+[Contact].[LastName]+",
"+[Contact].[Title]
AS NAME,
[Company].[CompanyName]
FROM Company INNER JOIN Contact ON
[Company].[CompanyID]=[Contact].[CompanyID]
WHERE NOT IsNull([Company].[Address]) AND NOT
IsNull([Contact].[Title]);

2) ==Query returning companies that have members with no title.
SELECT [Contact].[FirstName]+" "+[Contact].[LastName] AS NAME,
[Company].[CompanyName]
FROM Company INNER JOIN Contact ON
[Company].[CompanyID]=[Contact].[CompanyID]
WHERE NOT IsNull([Company].[Address]) AND IsNull([Contact].[Title]);

3) ==Query returning companies that have no members at all.
SELECT "Managing Broker" AS NAME,
[Company].[CompanyName]
FROM Company LEFT JOIN Contact
ON [Company].[CompanyID]=[Contact].[CompanyID]
WHERE [Contact].[LastName] IS NULL;

4) ==THIS ONE IS EXECUTING BUT NOT RETURNING CORRECT RESULTS. Query
should
return companies that have members but none with a management type of
title.
In this task, "Broker Associate" is the only non-management title.
SELECT "Managing Broker" AS NAME,
[Company].[CompanyName]
FROM Contact INNER JOIN Company ON Contact.CompanyID =
Company.CompanyID
WHERE (SELECT COUNT(*) FROM Contact WHERE ([Contact].[Title] <> "Broker
Associate" OR NOT IsNull([Contact].[Title])))>0