Why not do a join... something like this I expect...
><cfquery name="sermon_nums" datasource="maindsn" maxrows="5">
> SELECT sermons_details.sermon_num, count(sermons_details.sermon_num) AS
>tally
sermons.title etc, speakers.initials
> FROM sermons_details
JOIN sermons ON sermons_details.sermon_num = sermons.sermon_num
JOIN speakers ON sermons.speaker = speaker.full_name
> WHERE sermons_details.date_stamp >= #Last30Days#
> AND sermons_details.subject = 'SERMONACCESS'
> GROUP BY sermons_details.sermon_num
> ORDER BY count(sermons_details.sermon_num) DESC
></cfquery>
>
>This counts up the number of detail records, and orders them. Works like a
At 03:07 PM 4/17/02 -0400, you wrote:
>I need some help from your SQL gurus out there :)
>
>Here's the deal: we are keeping a running tally of the sermons which have
>been downloaded the most over the past 30 days. Every time a sermon is
>downloaded, I write a record out to a sermons_details table, where the
>primary key is the sermon number. This is working fine:
>
><cfset TestDate = CreateODBCDate(Now())>
><cfset Last30Days = '#DateAdd("d",-30,"#TestDate#")#'>
><cfquery name="sermon_nums" datasource="maindsn" maxrows="5">
> SELECT sermons_details.sermon_num, count(sermons_details.sermon_num) AS
>tally
> FROM sermons_details
> WHERE sermons_details.date_stamp >= #Last30Days#
> AND sermons_details.subject = 'SERMONACCESS'
> GROUP BY sermons_details.sermon_num
> ORDER BY count(sermons_details.sermon_num) DESC
></cfquery>
>
>This counts up the number of detail records, and orders them. Works like a
>champ. The difficulty comes in when I try to get the info from the header
>record for each sermon:
>
><cfquery name="most_requested" datasource="maindsn" maxrows="5">
> SELECT sermons.title, sermons.tape, sermons.name, speakers.initials,
>sermons.size
> FROM sermons, speakers
> WHERE tape IN (<cfloop query="sermon_nums">'#sermon_num#'<cfif
>nums.currentrow LT 5>,</cfif></cfloop>)
> AND sermons.size > 0 --->
> AND speakers.full_name = sermons.speaker
></cfquery>
>
>This gets the right info, but now the ordering is off -- the second query
>isn't ordered according to which sermon was downloaded most.
>
>I'm assuming there is a way to do all of this with a single query, but so
>far I haven't been able to figure it out. Oh yeah, I'm (still) using
>Access2000, so the functionality of the DB is a bit limited too. Ideas?
>
>TIA,
>David
>
>------------------------------------
>David Grabbe
>Manager, Information Systems
>Church of the Great God
>[EMAIL PROTECTED]
>http://www.cgg.org
>
>
>
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
