Update from array

Forum|Forum|17 years ago
October 15, 2008
3 replies
2617 views

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:

Database access

This topic has been closed for replies.

Correct answer

> So if a job is created on Sunday 10-12-08, then a script inserts the duration weeks
> labeled by "WeekEndingDate".

You realize there will be no "previous" record the very first week (10-19-2008). So you may want to use a LEFT JOIN and insert a default value when the "previous" hours do not exist.

Thanks! This worked. How do you recommed a default?

-

-__cfSearching__-

Inspiring

The ScareCrow wrote:
> Well it is MS Access and as I said it is weird, I hardly ever use it.

Neither do I. I was just confirming that the problem was not with the original join you suggested .. but with Access itself. I ran quick test and got the same syntax error as the OP. So it _does_ require special handling.

T

The_ScareCrow

Inspiring

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

-

-__cfSearching__-

Inspiring

Ken,

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

-

-__cfSearching__-

Inspiring

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?

A

Anonymous

SURE! Thanks for the reply cfsearching.

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.

-

-__cfSearching__-

Inspiring

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded