Using a value from a previous Record to Perform a Calculation?

[Guest]

I have a Append query that looks up a particular date range I specify and
gives me a count, for received and loaded dates that meet that range. From
those 2 numbers the query also subtracts Received - Loaded=Backlog. See below
ID Received Loaded Backlog New
Backlog
1 0 0 0
0
2 5 3 2
??????

What I need is for the query to lookup the New Backlog value from the
previous record and perform this calculation on the same query....
Previous New Backlog + New Received - New Loaded = New Backlog

I have used the formula below in the same append query however it does
return the value, it returns a blank to the table:
Expr1: DLookUp("[New Back Log]","tbl_TOTALS OPPD","[ID]=" &
[ID]-1)+ [Received] - [Loaded]

I append these values to the table (tbl_TOTALS OPPD). The query works for
everything but the New Backlog.

Any help will be GREATLY
APPRECIATED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[James A. Fortune]

I have a Append query that looks up a particular date range I specify and
gives me a count, for received and loaded dates that meet that range. From
those 2 numbers the query also subtracts Received - Loaded=Backlog. See below
ID Received Loaded Backlog New
Backlog
1 0 0 0
0
2 5 3 2
??????

What I need is for the query to lookup the New Backlog value from the
previous record and perform this calculation on the same query....
Previous New Backlog + New Received - New Loaded = New Backlog

I have used the formula below in the same append query however it does
return the value, it returns a blank to the table:
Expr1: DLookUp("[New Back Log]","tbl_TOTALS OPPD","[ID]=" &
[ID]-1)+ [Received] - [Loaded]

I append these values to the table (tbl_TOTALS OPPD). The query works for
everything but the New Backlog.

Any help will be GREATLY
APPRECIATED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

In:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/65b2425ca504beb9

I posted some SQL that dealt with data where the order entry mattered.

Note particularly Steve Jorgensen's post:

http://groups.google.com/group/comp.databases.ms-access/msg/8e89cbe86d787bfa

and my comment here:

http://groups.google.com/group/comp.databases.ms-access/msg/3d6c614fcab0fc93

"To summarize, using the order of the records to compare
values from the previous record(s) will often lead to nasty situations
like this."

The prospect of running sums is not much better. See:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/d1606d48c9a1fdb3

Lyle's comment about the report being the preferred solution is still valid.

I think Allen Browne's web site has an inventory database. Than might
have computations for OnHand or Backordered quantities obtainable from
table sums. Try to get around using the previous record first. If you
can't and the calculation is a simple one, a subquery might be able to
find the previous record and compute the value you're looking for. Post
back if you need help with this.

James A. Fortune
(e-mail address removed)