Inserting values from previous row into null cells

A

acccessaccess2003

I have the following query, qryCumulative
Week targetTotal actualTotal
1 3 3
2 5 nothing shown
3 9 4
4 nothing shown 6

An example of the query results that I wish to attain is this:
Week targetTotal actualTotal
1 3 3
2 5 3
3 9 4
4 9 6

I've tried creating an UPDATE query but Access shows me an error message
indicating that 'Operation must use an updateable query.'
The UPDATE query is as follows:
UPDATE qryCumulative SET qryCumulative.[targetTotal] =
DLookUp("[targetTotal)]","qryCumulative","[Week] = [Week]-1"),
qryCumulative.[actualTotal)] =
DLookUp("[actualTotal]","qryCumulative","[Week] = [Week]-1")
WHERE (((qryCumulative.[targetTotal]) Is Null)) OR
(((qryCumulativeTest.[actualTotal]) Is Null));

How can I insert/update the values from the previous row to the null cells
so that I can generate a continuous line chart and not have breaks in between
due to the null values?
 
J

John Spencer

Your problem is not the update query. It appears that qryCumulative is
not updatable. I suggest you post it for diagnosis.

My guess is that qryCumulative is totaling (sum, group by, etc.) and
that type of query is never updatable.

What problem are you trying to solve. If you are just trying to SHOW
data in a report, then you need to build a select query based on
qryCumulative or revised qryCumulative itself.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top