Solved Counting the number of records

[Teri Bridges]

Team, I have a query where I count the number of records in my task table (Count: TaskID) which returns 15 records this is correct. I have one record that is set to remove. I need to leave this record in my task table for tracking purposes but I do not want it in my over all count. I add the field Remove which is a check box and set the value to no. It removes it from the count. so far all looks good.

My next step is to perform a calculation on that count to get a percentage of over all work effort. So I created another query based on the count and say
ProjectStatus: [TaskCount]/Nz(DCount("*","tblTasks"),0)
But this is calculating on the 15 records not 14 (excluding the removal record) I need to make sure the records checked as removed are not counted.

Any suggestions? I think I have to add something saying -Removal.

 

[jdraw]

This sql should give the count. My sample has the Remove Yes/No field set to True. So you'd count where Remove is False (if I've understood your post).

SELECT Count(TaskID) AS CountOfRecords
FROM YourTableName
HAVING (((Remove)=False));

 

[theDBguy]

ProjectStatus: [TaskCount]/Nz(DCount("*","tblTasks"),0)

Try

ProjectStatus: [TaskCount]/Nz(DCount("*","tblTasks","Remove=False"),0)

 

[cheekybuddha]

Try

ProjectStatus: [TaskCount]/Nz(DCount("*","tblTasks","Remove=False"),0)

You don't need the Nz(). DCount() always returns a number

 

[The_Doc_Man]

Guys, that's great except that you should watch out for the zero case because that invites a Divide By Zero. Access won't care that the numerator is 0 if the denominator is also zero. It would throw an error anyway.

ProjectStatus: [TaskCount]/IIF( [TaskCount]=0, 1, DCount( "*", "tblTasks", "Remove=False") )

That should give you 0 when the task count is 0 or tblTasks is empty (which would probably mean that task count IS 0.) Or at least something like that.

 

[Teri Bridges]

Count("*") and dCount("*") count the number of records in the recordset. Count("somefield" and dCount("somefield") count the number of records with non-null values in "somefield". Neither take into consideration your special restriction. You would need to use the count on a query which does take the restriction into consideration and only returns the rows you want to count.

PS, there is something wrong with your design if you have to designate one record as special. Perhaps you need an additional table to act as parent for the group.

I am thinking you may be right. I need to track if the task is removed from scope or if the task was added to scope. This tracking scope changes. I need to put some thought into this. I am not sure I may need two tables. One table to establish the Scope change ID Add to or remove from then a table to track the results i.e., the Scope change ID, the date and the comments and then tie that ID to the main Task table. Still learning and trying to grasp the logic. so thank you for all your help.