Limiting .RecordCount?

Results 1 to 2 of 2

Thread: Limiting .RecordCount?

  1. #1
    Join Date
    Dec 1969

    Default Limiting .RecordCount?

    Couple of posts down, I asked about using RS.RecordCount. I was wondering if there&#039;s a way to limit that value returned. For example, let&#039;s say a RecordSet returns 4 values. In my ASP page, there&#039;s a value returned for OFFENSE which can be BEHAVIOR, GUM, UNPREPARED, etc. Is there a way to count the number of values returned for each offense separately? For example, at the bottom of the page, I would have each offense listed like this:<BR><BR>GUM: &#060;%=Number of records from Recordset with GUM as offense%&#062;<BR>UNPREPARED: &#060;%=Number of records from Recordset with UNPREPARED as offense %&#062;<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Yes, easy...

    ...but it can&#039;t be combined with the same query that, for example, gets full details of the records.<BR><BR>SELECT Offense, Count(*)<BR>FROM yourtable<BR>GROUP BY Offense<BR><BR>Presto.<BR><BR>That will return one record for each type of offense along with the count of records for that offense. <BR><BR>It won&#039;t return any zero counts if you only use one table, but with an OUTER JOIN against a table of all offenses it could be made to do so:<BR><BR>SELECT OffenseNames.Name, Count(yourtable.offenseID)<BR>FROM OffenseNames LEFT JOIN yourtable<BR>ON ( OffenseNames.offenseID = yourtable.offenseID )<BR>GROUP BY OffenseNames.Name<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts