CFLOOP within a CFLOOP not working

[psdiva]

i am trying to build a form page that outputs each employee's name, work preference, etc. based on the dates available to work. so far, the page will output the individual tables correctly - including the date. however, the data in the table will only reflect the FIRST date for each employee.
example: you have two dates, 15 employees, options of yes or no for overtime, early, over and charged. when i run the page i see the first table with the first date, all employees names and their choices. the second table shows the second date, all employees names and their choices for the FIRST date.
here's the code i'm using. i've tried several different things and i still cant get the data to output correctly. i've commented the code where the dates work and do not work.
any help will be greatly appreciated - i'm very new to looping.

<!--- shift query --->
<cfquery name=&quot;shift_list&quot; datasource=&quot;gtoqrcvs&quot; dbtype=&quot;ODBC&quot;>
select cdsid, wrkd, avail, orderby
from cs_names
where shift = '#shift#' and section = 'oper' <!--- and techno = 'n' ---> and techno < > 's'
order by avail, wrkd, orderby
</cfquery>
<!--- date query --->
<cfquery name=&quot;d&quot; datasource=&quot;gtoqrcvs&quot; dbtype=&quot;ODBC&quot;>
select id, date, type
from OT_dates
where date <> ''
</cfquery>
<cfloop query=&quot;d&quot;>
<table border=&quot;1&quot;>
<tr>
<td colspan=&quot;11&quot; align=&quot;center&quot;>#d.date#</td><!--- this date increments correctly --->
</tr>
<tr bgcolor=&quot;Silver&quot;>
<td>Shift  #shift#</td>
<td align=&quot;center&quot;>wrkd</td>
<td align=&quot;center&quot;>avail</td>
<td align=&quot;center&quot;>init</td>
<td align=&quot;center&quot;>pref</td>
<td align=&quot;center&quot;>acpt</td>
<td align=&quot;center&quot;>e</td>
<td align=&quot;center&quot;>eacpt</td>
<td align=&quot;center&quot;>o</td>
<td align=&quot;center&quot;>oacpt</td>
<td align=&quot;center&quot;>chg</td>
</tr>
<form action=&quot;OT_acpt_action.cfm&quot;>
<input type=&quot;hidden&quot; name=&quot;shift&quot; value=&quot;#shift#&quot;>
<input type=&quot;hidden&quot; name=&quot;date&quot; value=&quot;#d.date#&quot;><!--- this date increments correctly --->
<cfoutput query=&quot;shift_list&quot;>
<!--- this code will make table rows alternate in color - ffffff = white, whatever color you pick for the next row --->
<cfif shift_list.currentrow MOD 2 is 0><tr bgcolor=&quot;ffffff&quot;> <cfelse><tr bgcolor=&quot;eoffff&quot;></cfif>
<!--- this should pick each persons preferances based on the date from the d -query and the cdsid from the shift_list query --->
<cfquery name=&quot;pref&quot; datasource=&quot;gtoqrcvs&quot; dbtype=&quot;ODBC&quot;>
select cdsid, pref, e, o
from OT_list
where cdsid = '#cdsid#' and date = '#d.date#'
</cfquery> <!--- above date in query does NOT increment correctly ??? --->
<!--- above cdsid outputs once for each name and works correctly --->
<td>#cdsid#</td>
<td>#wrkd#</td>
<td>#avail#</td>
<td>#orderby#</td>
<td align=&quot;center&quot; width=&quot;30&quot;><b>#pref.pref#</b></td>
<td align=&quot;center&quot;>
<font face=&quot;Arial&quot; size=&quot;1&quot;>N<input type=&quot;radio&quot; checked name=&quot;a#cdsid#&quot; value=&quot;0&quot;>
<font face=&quot;Arial&quot; size=&quot;1&quot;>Y<input type=&quot;radio&quot; name=&quot;a#cdsid#&quot; value=&quot;1&quot;>
</td>
<td align=&quot;center&quot; width=&quot;30&quot;><b>#pref.e#</b></td>
<td align=&quot;center&quot;>
<font face=&quot;Arial&quot; size=&quot;1&quot;>N<input type=&quot;radio&quot; checked name=&quot;e#cdsid#&quot; value=&quot;0&quot;>
<font face=&quot;Arial&quot; size=&quot;1&quot;>Y<input type=&quot;radio&quot; name=&quot;e#cdsid#&quot; value=&quot;1&quot;>
</td>
<td align=&quot;center&quot; width=&quot;30&quot;><b>#pref.o#</b></td>
<td align=&quot;center&quot;>
<font face=&quot;Arial&quot; size=&quot;1&quot;>N<input type=&quot;radio&quot; checked name=&quot;o#cdsid#&quot; value=&quot;0&quot;>
<font face=&quot;Arial&quot; size=&quot;1&quot;>Y<input type=&quot;radio&quot; name=&quot;o#cdsid#&quot; value=&quot;1&quot;>
</td>
<td align=&quot;center&quot;>
<font face=&quot;Arial&quot; size=&quot;1&quot;>N<input type=&quot;radio&quot; name=&quot;c#cdsid#&quot; value=&quot;0&quot;>
<font face=&quot;Arial&quot; size=&quot;1&quot;>Y<input type=&quot;radio&quot; checked name=&quot;c#cdsid#&quot; value=&quot;1&quot;>
</td>
</tr>
</cfoutput><!--- ends output of shift_list query --->
<tr>
<td colspan=&quot;11&quot; align=&quot;center&quot;><input type=&quot;submit&quot; name=&quot;submit&quot;><input type=&quot;reset&quot; name=&quot;reset&quot;></td>
</tr>
</form>
</table>
<!--- a NEW table begins with the next date from the d query --->
</cfloop> <!--- end of d loop --->

 

[CFDude]

There is a known issue with using a query variable in a nested loop. The workaround is to set the variable to a temporary variable inside the loop and use that temp variable instead.

Try this:

<cfset tmpDate = d.date>
<cfquery name=&quot;pref&quot; datasource=&quot;gtoqrcvs&quot; dbtype=&quot;ODBC&quot;>
select cdsid, pref, e, o
from OT_list
where cdsid = '#cdsid#' and date = '#tmpDate#'
</cfquery>

 

[psdiva]

thanx a bunch CFDUDE!
we set the date and used the new variable name - works like a charm.
thanx again!
diva