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));

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

One table will suffice. Add two fields. A status field and a date field. When something is removed, change the status to "Removed" and add a date to the StatusDT field. When something is added after the fact (you can tell by the date of the parent record), then automatically set status to "Added" and the StatusDT to Date().

You should probably add a default status and run an update query to update all existing rows.

Then most queries will select rows where Status <> "Removed". However, the edit form will need to show all rows so the "Removed" can be readded UNLESS, you want to track the readd and make it a separate record.

I can see that the status values might get complicated and you might end up with more than one reason to not include a row. To handle that, I would make a status table and include an ActiveYN flag. So you can distinguish the Active status' from the inactive ones and rather than referring to a specific status in the queries, refer to the ActiveYN flag. You will either select ActiveYN = True or select all. I doubt you will need a query that just selects inactive items but you could if you needed to.

 

[Pat Hartman]

@Teri Bridges

You're new here. Welcome. If one of the suggestions solved your problem, add a note to that effect and then mark the thread solved. This will be a great help to the people who find the thread by searching.