Update Current Row based on Previous Row

  • Thread starter Thread starter AAVF
  • Start date Start date
A

AAVF

I have a small table that need to keep a balance of work outstanding. The
fields are as follows

REC_COUNT (auto-index created record counter)
DATE (work date)
QTY_REQD (work added on that date)
BAL (balance of work outstanding at the end of that date)

The field REC_COUNT is used as a sort order field so that there is no
confusion about which record is next.

I need to be able to read the BAL from the previous record and add to it the
the QTY_REQD. I have tried using a query (I am not a VB programmer) using
'Dlookup' where the REC_COUNT is the current REC_COUNT-1 but cannot get this
to work. I have tested the theory of the DLookup OK by returning the
REC_COUNT from the previous record. I assume this works as the REC_COUNT
already exists for all records. However, the BAL field is null until this
query runs.

Can anyone throw any light on how I can achieve this with a query?

Regards

Tom
 
Hi Tom,

Here's a couple of knowledge base articles that should help

ACC2000: Referring to a Field in the Previous Record or Next Record
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504

ACC2000: How to Create Running Totals in a Query
http://support.microsoft.com/default.aspx?scid=kb;en-us;208714

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

--------------------
| From: "AAVF" <[email protected]>
| Newsgroups: comp.databases.ms-access,microsoft.public.access.queries
| Subject: Update Current Row based on Previous Row
| Date: Wed, 21 Jan 2004 16:33:43 -0000
| Organization: AAVF
| Lines: 26
| Message-ID: <[email protected]>
| NNTP-Posting-Host: nnrp1.phx1.gblx.net
| X-Trace: node21.cwnet.roc.gblx.net 1074702760 86976 64.214.31.40 (21 Jan
2004 16:32:40 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Wed, 21 Jan 2004 16:32:40 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Cache-Post-Path: [email protected]
| X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/)
| Cache-Post-Path: nnrp1.phx1.gblx.net!212.104.129.36
| X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/)
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.su
l.t-online.de!t-online.de!news.tele.dk!news.tele.dk!small.news.tele.dk!news-
out.visi.com!petbe.visi.com!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!n
ews.globalcrossing.net!not-for-mail
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:187062
| X-Tomcat-NG: microsoft.public.access.queries
|
| I have a small table that need to keep a balance of work outstanding. The
| fields are as follows
|
| REC_COUNT (auto-index created record counter)
| DATE (work date)
| QTY_REQD (work added on that date)
| BAL (balance of work outstanding at the end of that date)
|
| The field REC_COUNT is used as a sort order field so that there is no
| confusion about which record is next.
|
| I need to be able to read the BAL from the previous record and add to it
the
| the QTY_REQD. I have tried using a query (I am not a VB programmer) using
| 'Dlookup' where the REC_COUNT is the current REC_COUNT-1 but cannot get
this
| to work. I have tested the theory of the DLookup OK by returning the
| REC_COUNT from the previous record. I assume this works as the REC_COUNT
| already exists for all records. However, the BAL field is null until this
| query runs.
|
| Can anyone throw any light on how I can achieve this with a query?
|
| Regards
|
| Tom
|
|
|
 
According to the Perv Scanner Stats, you are a prolific
cross-poster.

Don't excessively cross-post
Cross-posting refers to posting a message to more than one group at a time,
in the same post.
If a message truly belongs in multiple groups, by all means cross-post. In
fact it is better to cross-post than to post the same message separately to
different groups.
Be careful when replying to a post. The initial post may have been
inappropriately cross-posted; if you do not edit the list of newsgroups,
your reply will also be inappropriately cross-posted.
Consider setting "followups" to one group. Your post may be relevant to 3
groups, but you can set followups to one group and then the discussion will
continue there. When using the followup header, it is nice to put in the
message somewhere "Followups set."




AAVF scriveva:
 
Please ignore the posting from the "starwars" persona. That person has some
problems.

Crossposting to several relevant newsgroups, such as you have, is perfectly fine.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Thanks for your advice.

I am not sure who "starwars" is. As far as being "a prolific cross-poster"
is concerned, I find that a bit strange as I have only posted three messages
in the last six months or so. This one seemed appropriate to cross-post as
it was query-specific and Access-specific. I did post it to two groups
separately as I forgot to put the second group in the first posting (if you
follow my drift).

Regards

Tom
 
I suggest that you look up the definition of 'prolific'. No more than half a
dozen postings in about six months, admittedly all cross-posted, does not
seem prolific to me.

I am not sure if your reply is beating me up or offering advice. You seem to
be saying 'don't do it, but do it if it is OK'. If you read my post, you
would see that it has been sent to two groups, one that is 'generic' Access,
and one that is Access queries related. That is hardly flooding the market.

Tom
 
Hi Eric

Thanks for the pointers.

The first KB article you mention (Referring to a Field in the Previous
Record or Next Record) was the one that reminded me to use DLookup in the
first place, along with setting an AutoCounter to ensure correct sorting. I
will have a look at the second one (How to Create Running Totals in a Query)
and see if that solves the main problem of a running sum.

Regards

Tom
 
Hi Eric

So far, so good.

I can now create a running sum on a particular row based on the value of the
record counter. However, I need now to take this a stage further, which may
mean I am using the wrong method. I need to have a running sum, but I also
need to be able to deduct from it a set value (stored in another field).
What we are doing is trying to see what workload an area has (QTY_REQD) and
accumulate new work as it goes along (hence the running sum), but deduct
from it the known capacity for each day (in this case, a value of 170 for
weekdays and 0 for weekends: the value is stored in another field called
MAX_CAP).

I am going to experiment using the creation of a temporary table, but I am
not sure this will work.

If you have any better ideas, I would be grateful.

Regards

Tom
 
Hi Eric

I think I have cracked it, without using a temporary table. I simply do a
running sum on the MAX_CAP and deduct this from the running sum of
BAL+QTY_REQD. It seems to work.

Many thanks for your help.

Regards

Tom
 
Not as clever as I thought!

The problem with my theory is that it creates negative capacities, ie if the
running sum has 170 deducted from it on a daily basis, if there is no new
work and the current workload is only 152 units, at the end of the day the
current requirement is -18 instead of zero. If there is no new work the next
day, it goes to -188. When new work comes in, it is added to this running
sum. If the running sum is currently -188 and a job of 225 comes in, the new
running sum is (-188+225-170)=-133 instead of (0+225-170)=55. I am now not
sure that DSum is the answer.

Any ideas?
 
Back
Top