Counting with an inclusion list

[Chad Reid]

Okay, I have a query that generates a list of ClientID's that I want
included in a count. Take a look at the following query and make any
changes that you see fit to make it operational.

SELECT [CR - tbl Client Record, More Information].MaritalStatusID,
Count([CR - tbl Client Record, More Information].MaritalStatusID) AS
CountOfMaritalStatusID
FROM [CR - tbl Client Record, More Information]
GROUP BY [CR - tbl Client Record, More Information].MaritalStatusID
HAVING MaritalStatusID <> null AND
([CR - tbl Client Record, More Information].ClientID IN
(SELECT [STS - ApplicableClients].ClientID FROM [STS - ApplicableClients])
);

Thanks!

 

[[MVP] S. Clark]

Is there an error message or unexplained results?

"<> Null" may not net desired results. "Is Not Null" is typical syntax.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[HSalim]

All you have to change in your query is the <> Null. You cannot Compare or
equate to Null. You can only ask for Is Null or Is Not Null

However, a where clause would be more efficient.
Your table names leave a little to be desired - very hard to understand, but
hey, if it works for you - fine.
You could also Alias your table names to minimize typing those long names.
See below:


SELECT MI.MaritalStatusID, Count(MI.MaritalStatusID) AS
CountOfMaritalStatusID
FROM [CR - tbl Client Record, More Information] MI
INNER JOIN [STS - ApplicableClients] AC
ON MI.ClientID = AC.ClientID
WHERE MaritalStatusID is not null
GROUP BY MI.MaritalStatusID