Convert SQL query into Access query

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

On 8 Jan 2007 10:25:43 -0800, wrote:

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]

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
..

wrote in message
oups.com...
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

Alanna:

There seem to be a lot of unnecessary aliases and subqueries here. I'd have
thought it could be simplified to:

SELECT
CompanyName,
COUNT(*) AS NumberOfClientsWithNoIPS
FROM tblClients, tblCompanies, tblIPS
WHERE tblClients.RIACompanyID = tblCompanies.CompanyID
AND tblClients.ClientID = tblIPS.ClientID
GROUP BY CompanyName
UNION ALL
SELECT
'zz - Total',
COUNT(*)
FROM tblClients, tblCompanies, tblIPS
WHERE tblClients.RIACompanyID = tblCompanies.CompanyID
AND tblClients.ClientID = tblIPS.ClientID
ORDER BY CompanyName;

Ken Sheridan
Stafford, England

" wrote:

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

Alanna :

Oops! It was NOT IN, so the subqueries *are* necessary. I'd suggest using
the NOT EXISTS predicate, however. SQL programmers generally favour the
latter on the basis that it allows the optimizer maximum discretion in the
use of the indexes:

SELECT
CompanyName,
COUNT(*) AS NumberOfClientsWithNoIPS
FROM tblClients, tblCompanies, tblIPS
WHERE tblClients.RIACompanyID = tblCompanies.CompanyID
AND NOT EXISTS
(SELECT *
FROM tblIPS
WHERE tblIPS.ClientID = tblClients.ClientID)
GROUP BY CompanyName
UNION ALL
SELECT
'zz - Total',
COUNT(*)
FROM tblClients, tblCompanies, tblIPS
WHERE tblClients.RIACompanyID = tblCompanies.CompanyID
AND NOT EXISTS
(SELECT *
FROM tblIPS
WHERE tblIPS.ClientID = tblClients.ClientID)
ORDER BY CompanyName;

Ken Sheridan
Stafford, England

" wrote:

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