Counting instances in a table

Rams_ · Apr 1, 2008

I would like to know a method of looping through a recordset, and counting the number of times something occurs (in my case how many times a foriegn key occurs). I want to count each foriegn key though, not just one.

My foriegn key is called "vet id", the table is tblanimals.

So say I had three animals, two animals had a FK vet ID of 2 and the other animal had an FK Vet ID of 1 so my page would display a count of two for vet 2, and one for vet 1.

I am thinking that I need to capture each vet ID in a variable from it's origin table (tblvets) and then loop through each record in the animals table adding one to the variable count but I am not sure how to achieve this.

Thanks

pbaldy · Apr 1, 2008

A query would probably be simplest:

SELECT VetID, Count(VetID) AS HowMany
FROM TableName
GROUP BY VetId

Rams_ · Apr 4, 2008

Thanks for that!

Have since changed my query to include Vet IDs not present as FKs but it returns NULL rather than 0 as the result.

Trying to convert to Null to "0" is proving problamatic. It seems to think every x.value=NULL and my table comes out with just 0's

Code:

<!--			CONTENT OF TABLE-START	-->

<s1>Vet Popularity</s1><br>
<br><br>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("kennels.mdb"))

set rs = Server.CreateObject("ADODB.recordset")

rs.Open "SELECT tblVets.vetid, intCount FROM tblVets LEFT JOIN (SELECT tblAnimals.vetid, COUNT(tblAnimals.vetid) AS intCount FROM tblAnimals GROUP BY tblAnimals.vetid) vetCount ON (tblVets.vetid = 

vetCount.vetid)", conn
%>

<table class="results-table">
<tr>
<th>Vet ID</th>
<th>Number of Times Vet Registered</th>
</tr>
<% do until rs.EOF %>
<tr>
<% for each x in rs.Fields %>
<td>
<%
' If statement to convert NULLS into 0's

If x.value<>NULL then
Response.Write(x.value)%>
</td>
<% else
Response.Write 0%>
</td>
<% end if 
next
rs.MoveNext %>
</tr>
<% loop %>
</table>

<!--			CONTENT OF TABLE-END	-->

pbaldy · Apr 4, 2008

Can you post a sample db? If I try a LEFT JOIN, the ID's with no records in the second table have a zero count, not Null:

SELECT tblEmployee.ID, Count(tblEmployeeActivity.ID) AS CountOfID
FROM tblEmployee LEFT JOIN tblEmployeeActivity ON tblEmployee.ID = tblEmployeeActivity.ID
GROUP BY tblEmployee.ID

Also, you can't normally test for null with = or <>, as Null is probably best described as unknown. Try this if you really have Nulls:

If IsNull(x.value) then

Rams_ · Apr 6, 2008

The ISNull worked, thanks!

Counting instances in a table

Rams_

New member

pbaldy

Wino Moderator

Rams_

New member

pbaldy

Wino Moderator

Rams_

New member

Similar threads

Users who are viewing this thread