Hi,
I need to produce a report in CF that extracts a range of values repeatedly from a database and displays the record count.
For example I need to extract the recordcount where a record has a PowerRating value of between 0 - 2.9, and 3 - 7.4 and so on.
I can create multiple queries:
- select ClientFK,
-
ResponseLevelFK,
-
PowerRating
-
from tblWorkshopsNeil
-
where ClientFK like '%#form.ClientFK#%'
-
and (((tblWorkshopsNeil.PowerRating)>=0 And (tblWorkshopsNeil.PowerRating)<=2.9))
-
order by ClientFK
-
-
-
</cfquery>
-
-
-
<cfquery datasource="repairmdb" name="qryKWBand2">
-
select ClientFK,
-
ResponseLevelFK,
-
PowerRating
-
from tblWorkshopsNeil
-
where ClientFK like '%#form.ClientFK#%'
-
and (((tblWorkshopsNeil.PowerRating)>=3 And (tblWorkshopsNeil.PowerRating)<=7.4))
-
order by ClientFK
-
-
-
</cfquery>
and just return the recordcounts:
- <cfoutput>#qryKWBand1.RecordCount#</cfoutput></p>
-
<p>Number of records returned: <cfoutput>#qryKWBand2.RecordCount#</cfoutput></p>
..but this seems really inefficient, and as I might need to add in further filters (such as breaking down the results by Response Level) very cumbersome.
Is there a way to run a major query then use the basis to perform further queries?
Thanks
Neil
