Counting # of Records

flying linus

Registered User.
Local time
Today, 02:28
Joined
Oct 1, 2004
Messages
33
I have a table that shows the the status of the record as "Not Started", "In Work" or "Complete"

I want to count the number of records that fall into each category. I am currently doing the following SQL:

SELECT tblTest_Case_Status.Test_Case_Status, Count(*) AS NumberofRecords
FROM tblAV_Fail_Cases, tblTest_Case_Status
GROUP BY tblTest_Case_Status.Test_Case_Status;

However, it displays the status, but the count for each status is the total record count, not a breakdown of each. FOr instance, if I have 33 records, the count for each status is 33, not as expected:

Not Started 20
In Work 13
Complete 0

I am new to this so any help is appreciated.
 
You have an extraneous table in the query. Since there is no proper join, Jet is making a cartesian product. Remove the table or if you need it, fix the join.
 
OK...so I have the table with the categories listed above. The categories are chosen via Combobox that is linked to another table. How would I fix the join?

tblAV_Fail_Cases - table that I want to perform the count on
tbl_Test_Case_Status - table that the combobox displays in the above table
 
Are the two tables related by a StatusID field? If they are, then switch the query to Design View and drag the StatusID field from one table to the other.
.
 
OK, it works but it is not showing those with a count of zero. How do I get it to show those? My end product is a report that shows metrics and those categories with a count of zero is required by the end user.
 
Since table tblAV_Fail_Cases doesn't contain all the three categories, the query needs an outer join and needs to count the StatusID of table tblAV_Fail_Cases, i.e.

SELECT tblTest_Case_Status.Test_Case_Status, Count(tblAV_Fail_Cases.StatusID) AS NumberofRecords
FROM tblAV_Fail_Cases RIGHT JOIN tblTest_Case_Status ON tblAV_Fail_Cases.StatusID = tblTest_Case_Status.StatusID
GROUP BY tblTest_Case_Status.Test_Case_Status
ORDER BY tblTest_Case_Status.Test_Case_Status DESC;
.
 
Thanks, that worked.

Now, how do I also add a Total Count to the same query? Is that even possible?
 
You can add the total count like this:-

SELECT tblTest_Case_Status.Test_Case_Status, Count(tblAV_Fail_Cases.StatusID) AS NumberofRecords
FROM tblAV_Fail_Cases RIGHT JOIN tblTest_Case_Status ON tblAV_Fail_Cases.StatusID = tblTest_Case_Status.StatusID
GROUP BY tblTest_Case_Status.Test_Case_Status
UNION Select "Total", Count(*) from tblAV_Fail_Cases


The query results will be in ascending order of the first column i.e.
Code:
Test_Case_Status  NumberofRecords
Complete	  	 0
In Work			13
Not Started		20
Total		 	33

In fact, you can add the total in the report.
.
 

Users who are viewing this thread

Back
Top Bottom