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?
 
rbeadieConnect With a Mentor Commented:
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
 
keschusterAuthor Commented:
YOU DA MAN!  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.