Jun 3 2008, 07:51 AM
I have records based by individuals who have documents assigned to them to work on. I would like to build a query where it lists the employee, total amount of documents, and total amount of documents incomplete.
created a query listing the information and I utilize the total function, grouping by Employee and counting total documents, but I can't figure out a function to use to only count records that are "open".
Any help would be greatly appreciated.
Jun 3 2008, 08:25 AM
What you can do is create an expression column that will return a 1 if the document is complete and a 0 if not, then you SUM that column ... in SQL it would look something like this:
SELECT PersonID, Count(*) As TotalDocuments, Sum(IIf([DocCompleteDate] Is Null, 0, 1)) As TotalComplete
GROUP BY PersonID
Note that I assumed the document is marked complete by a date field, you will have to adjust for your situtation (ie: Testing a Yes/No field or whatever the case may be)
Jun 3 2008, 08:47 AM
Such a simple and efficient solution.
Thank you so much it worked great
Jun 3 2008, 06:57 PM
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here