I am trying to update multiple records in one table with multiple values. I
have a feeling that I am going in the wrong direction with what I am working
with but I have tried numerous attempts. I am hoping you can help me. What I
have is a column of "Week Ending Dates" (all Sundays). These dates have to have
a value entered weekly in another column of "Earned" values. There is also
another column of "Percent Complete" values which needs to be entered weekly.
Problem that I am trying to solve is there is a possiblily that these values
may not be updated weekly for a number of reasons. I would like to query the
data table for the week prior and update the value with that prior week value
automatically every Sunday evening. I haev been working with this code:



<cfset TodaysDate = #CreateODBCDate(Now())#>
<!--- Get the raw data from the database. --->
<CFQUERY DATASOURCE="db" name="Prod">
SELECT Earned, PercentComplete
FROM Productivity
WHERE WeekEndingDate < #dateadd("d",0,todaysDate)#
AND WeekEndingDate > #dateadd("d",-7,todaysDate)#
</cfquery>

<!--- Declare the array --->
<cfset myarray=arraynew(2)>
<!--- Populate the array row by row --->
<cfloop query="Prod">
<cfset myarray[currentrow][1]=Earned[currentrow]>
<cfset myarray[currentrow][2]=PercentComplete[currentrow]>
</cfloop>

<!--- Now, create a loop to output the array contents --->
<cfset total_records=Prod.recordcount>
<cfloop index="Counter" from=1 to="#Total_Records#">
<cfset Earn = "#MyArray[Counter][1]#">
<cfset Perc = "#MyArray[Counter][2]#">
<CFQUERY Name="Blanks" DATASOURCE="db" >
UPDATE Productivity
SET Earned = #Earn#,
PercentComplete = #Perc#
WHERE Earned IS NULL
AND WeekEndingDate < #dateadd("d",7,TodaysDate)#
AND WeekEndingDate > #dateadd("d",0,TodaysDate)#
</CFQUERY>
</cfloop>