Count Distinct Records For An Access Database And Active
Server Pages
Count Unique Records - Filter Access Database Records Select
Count
I used ASPmaker
to generate my main page at
http://quarter-horse-times.com/horse-show/VIEWCLASSES.asp but I wanted to
show a count of the number of horses entered in each class. Getting a count of
the total number of FILES in each class was easy. The hard part for me was
making the count distinct so that all the files for one horse were counted as
one, thereby giving me the total number of horses entered in each class.
I used Buildapp to create the code for an array that I could copy and paste into my ASPmaker generated table. But how to filter the TOTAL number of horses entered in each class? I soon learned that Access does not support SELECT (DISTINCT column).
After much trial and error and surfing of forums, I came up with the following that works like a charm. I am posting it here in case it might be helpful to someone else. If you find it useful, please send me an email, Lil at horsevu.com.
| <% Dim strcnt 'this is to place the horse show class number into a variable strcnt=rs("ORDERID") 'this recordset is from one that was created higher up in the code on my horse show page. %> <% Dim objRS,strDBPath,objConn,strSQL,x,arrData,strFieldValue Dim intcolcounter,introwcounter,strPageType,strOrderBy Dim intpages,pageno,intRecordCnt,pagesize,intPage,strheadings,intRecord 'The
following method I found on a forum, filters the records so that all of the
files in a horse show class that are associated with one particular horse
will be counted only once as a single entry. |
SELECT count(HORSEENTRYNUMBER) AS
TOTAL
FROM [Select distinct HORSEENTRYNUMBER from SHOWPLACEMENTS].
AS HORSENUM
'I don't know why but we get an error if we don't include the period after SHOWPLACEMENTS with "as horsenum"
My other tutorials:
