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>

chrispilie wrote:
> 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.

This is confusing. What do you mean by "the week prior" and update it with
"that prior week value". Can you provide an example with actual dates?

I think you may be making this harder than is required.

How do the record for the current week get inserted into the table ?

This can all be done with just the use of sql.
Here are a couple of examples, untested.

If you want to select the values from the previous week and insert then into
the table with this weeks end date then,
Insert Into Productivity(WeekEndingDate, Earned, PercentComplete)
Select #CreateODBCDate(TodaysDate)#, Earned, PercentComplete
From Productivity
Where WeekEndingDate = #CreateODBCDate(dateadd("d",-7,todaysDate))#

But if the record is already inserted and you just need to update it then

UPDATE Productivity
SET Earned = R.Earned
, PercentComplete = R.PercentComplete
FROM Productivity, Productivity R
WHERE R.WeekEndingDate = #CreateODBCDate(dateadd("d",-7,todaysDate))#
AND Productivity.WeekEndingDate = #CreateODBCDate(TodaysDate)#

Ken

Ken,

Very good answer. I was trying to figure out the "prior" versus "prior" dates first ;-) but your suggestion sounds exactly right.

Thanks! SQL has been my weekness. I actually tried a couple of statements much
like the one you are recomending that I found. They had been kicking out
errors. Most of them were syntax errors so I figured Access wasn't accepting
it. I would love to use one like this. I am getting this error however.

Syntax error (missing operator) in query expression 'R.PercentComplete FROM
Productivity'.

A little more info:
I am using Access if that helps. I do have the date field set as a date/time
data type. I am trying to update and existing row.

SURE! Thanks for the reply.

Lets say that the last update of "Earned" hours was on "WeekEndingDate"
10-5-08. The Earned value was 100 hours. The way my code is set up is that an
entry can be made any time within the date range of 9-28-08 to 10-5-08 and that
"Earned" value would be updated on "WeekEndingDate" 10-5-08. Now this week a
Project Engineer may not update a value this week because nothing has been
earned. This will break the program. I have to update this table automatically.
So in the date range of 10-5-08 to 10-12-08, I need to look at the
"PercentComplete" value for "WeekEndingDate" 10-12-08 in an Update Query
(preferably) and search for a NULL value. When the query finds those records. I
need to update that record with the weeks prior
(WeekEndingDate=10-5-08;Earned=100)"Earned" and "PercentComplete" value. I hope
I didn't confuse you more.

The null value is important.

chrispilie wrote:
> I am getting this error however.

Ken's suggestion is on target. While he mentioned it is untested, the syntax
looks correct to me. At least for databases like MS SQL. MS Access seems to
require some tweaking. The best adaptation I could come up with was something
like this. But I am very rusty on Access. So I am sure it could be improved.


UPDATE Productivity AS p, Productivity AS R
SET p.Earned = R.Earned,
p.PercentComplete = R.PercentComplete
WHERE R.WeekEndingDate = #CreateODBCDate(dateadd("d",-7,todaysDate))#
AND p.WeekEndingDate = #CreateODBCDate(TodaysDate)#
AND p.Earned IS NULL

chrispilie wrote:
> in an Update Query (preferably)

Assuming the records already exist, and there is only a single record for each week, an update should work.

MS Access is really weird and requires brackets
Something like the following should work

UPDATE Productivity
SET Earned = R.Earned
, PercentComplete = R.PercentComplete
FROM ((Productivity INNER JOIN Productivity R ON R.WeekEndingDate =
#CreateODBCDate(dateadd("d",-7,todaysDate))#)
AND Productivity.WeekEndingDate = #CreateODBCDate(TodaysDate)#)

Or

UPDATE Productivity
SET Earned = R.Earned
, PercentComplete = R.PercentComplete
FROM Productivity INNER JOIN Productivity R ON R.WeekEndingDate =
#CreateODBCDate(dateadd("d",-7,todaysDate))#
WHERE Productivity.WeekEndingDate = #CreateODBCDate(TodaysDate)#

The ScareCrow wrote:
> Something like the following should work

I do not think Access likes that join type with updates. At least not my
version. It does work if you place the join after the UPDATE:

UPDATE Productivity INNER JOIN Productivity R ON Productivity.WeekEndingDate =
DateAdd("d",-7,R.WeekEndingDate)
SET Productivity.Earned = R.Earned
, Productivity.PercentComplete = R.PercentComplete
WHERE Productivity.WeekEndingDate = Date()

Though using dateAdd(..) probably eliminates the possibility of using indexes.
But assuming it is a small table it probably would not make a noticeable
difference.