Convert SQL query into Access query

[amsablotny]

I am trying to change this sql statment into a an access sql statement.
I am not sure how convert the where statement "where c.ClientID not in
(Select clientID from tblIPS)" into access.


select co.CompanyName, count(c.ClientName) NumberOfClientsWithNoIPS
from tblClients c
join tblCompanies co on c.RIACompanyID = co.CompanyID
where c.ClientID not in (Select clientID from tblIPS)
Group By CompanyName
union
select 'zz - Total', count(c.ClientName) Total
from tblClients c
join tblCompanies co on c.RIACompanyID = co.CompanyID
where c.ClientID not in (Select clientID from tblIPS)
order by CompanyName

Can someone help?

Thanks,

Alanna

 

[John Vinson]

I am trying to change this sql statment into a an access sql statement.
I am not sure how convert the where statement "where c.ClientID not in
(Select clientID from tblIPS)" into access.

Erm? Subqueries like this work in Access, though they are slow. What
error are you getting?

Try a "frustrated outer join" instead:

select co.CompanyName, count(c.ClientName) NumberOfClientsWithNoIPS
from (tblClients c
join tblCompanies co on c.RIACompanyID = co.CompanyID)
left join tblIPS on tblIPS.ClientID = c.clientID
where tblIPS.ClientID IS NULL
Group By CompanyName
union
select 'zz - Total', count(c.ClientName) Total
from (tblClients c
join tblCompanies co on c.RIACompanyID = co.CompanyID)
left join tblIPS ON tblIPS.ClientID = c.clientID
where tblIPS.clientID is null
order by CompanyName

John W. Vinson[MVP]

 

[John Spencer]

That should work with the exception that you will probably need to use the
AS word for your field alias and the Union will ignore the TOTAL alias in
the second query clause and will use NumberOfClientsWithNoIPS as the column
name.

select co.CompanyName
, count(c.ClientName) AS NumberOfClientsWithNoIPS
from tblClients c
join tblCompanies co on c.RIACompanyID = co.CompanyID
where c.ClientID not in (Select clientID from tblIPS)
Group By CompanyName
union
select 'zz - Total'
, count(c.ClientName) AS Total
from tblClients c
join tblCompanies co on c.RIACompanyID = co.CompanyID
where c.ClientID not in (Select clientID from tblIPS)
order by CompanyName


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..