SharePoint Moss & MS Access - Query with Grouping not working

I've got a sharepoint list linked to and access 2007 database.  I'm trying to get a grouped count with this query

SELECT [Client Data Loaders].COE, Count([Client Data Loaders].[CS Doc Complete]) AS [CountOfCS Doc Complete]
FROM [Client Data Loaders]
GROUP BY [Client Data Loaders].COE
HAVING (((Count([Client Data Loaders].[CS Doc Complete])) Not In (0)));

This returns nothing.  The field CS Doc Complete is a Yes/No field.
If I just pull up the table and filter CS Doc Complete = Yes - then look in Advanced Filter I get the Not In (0).  Now I would think using this in a query would return a count of records where CS Doc Complete = yes grouped by COE.

Any ideas?  Is there a trick to this when linked to SharePoint?
keschusterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rbeadieCommented:
Using the HAVING clause will remove any results that have a count that isn't zero, which is why you aren't getting results.  Try the following:

SELECT [Client Data Loaders].COE, Count([Client Data Loaders].[CS Doc Complete]) AS [CountOfCS Doc Complete]
FROM [Client Data Loaders]
WHERE ([Client Data Loaders].[CS Doc Complete]) Not In (0)
GROUP BY [Client Data Loaders].COE;

The WHERE clause acts before the group by; the HAVING clause acts after the aggregation of GROUP BY
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
keschusterAuthor Commented:
YOU DA MAN!  Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.