Multiple querys OR Querying query

[ndeeley]

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

[acoder]

Yes, there's a feature in Coldfusion called Query of Queries.

[ndeeley]

Thanks acoder - great!

Neil

[acoder]

No problem, you're welcome :)