Full Version: Counting Records
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
SClen
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.

I 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.
datAdrenaline
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
FROM tblDocuments
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)
SClen
Such a simple and efficient solution.

Thank you so much it worked great
datAdrenaline
Excellent! ...thumbup.gif
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.