Solved Counting the number of records (1 Viewer)

Teri Bridges

Member
Local time
Today, 09:21
Joined
Feb 21, 2022
Messages
186
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

Super Moderator
Staff member
Local time
Today, 10:21
Joined
Jan 23, 2006
Messages
15,364
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).

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

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:21
Joined
Feb 19, 2002
Messages
42,983
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:21
Joined
Feb 28, 2001
Messages
27,003
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.

Code:
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

Member
Local time
Today, 09:21
Joined
Feb 21, 2022
Messages
186
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

Super Moderator
Staff member
Local time
Today, 10:21
Joined
Feb 19, 2002
Messages
42,983
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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:21
Joined
Feb 19, 2002
Messages
42,983
@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.
 

Users who are viewing this thread

Top Bottom