Get Value from Previous Record

  • Thread starter Thread starter Bernie
  • Start date Start date
B

Bernie

I have a continuous form. I would like to establish a
field either in the query or form text box to obtain a
quantity value from the previous record. Ex:

Rec Date Avail On Order Demand Balance
1 8/28 100 200 50 250
I would then like the Balance from Rec 1 to be set as the
Avail balance for the next record and continue this:
2 9/05 250 100 50 300
3 9/12 300 50 100 250

Bernie
 
Hi,


SELECT a.[Date], a.Avail, a.OnOrder, a.Demand,
SUM(b.avail+b.OnOrder-b.Demand)

FROM myTable As a INNER JOIN myTable As b
ON a.[date] >= b.[date]



where "b" is concerned about records relative to operation occurring
before, or "at" the same moment than "a", so, it is then a matter to SUM
these.




Hoping it may help,
Vanderghast, Access MVP
 
Hi,


SELECT a.lot, LAST(a.processingTime), #09/01/2004# + SUM(
Nz(b.processingTime.0))
FROM myTable As a LEFT JOIN myTable as b
ON a..Lot > b.Lot
GROUP BY a.lot




Hoping it may help,
Vanderghast, Access MVP





Luis Marrero said:
I have the same inquiry but using the values in a table. I am using the
Dlookup function but I get wrong results. I am trying to calculate a new
start date based on the processing time of the previous lot. I have the
following original table:

Lot Processing Time Start Date
1 5 09/01/04
2 8 09/01/04
3 9 09/01/04
4 10 09/01/04

I expect to have the following results:

Lot Processing Time New Start Date
1 5 09/01/04
2 8 09/06/04
3 9 09/14/04
4 10 09/23/04

I am using the function: New Start Date: Dlookup("[Start Date]+[Processing
Time]", "TableName", "[Lot]=" &[Lot]-1)

But the results that I am getting are:

Lot Processing Time New Start Date
1 5
2 8 09/06/04
3 9 09/09/04
4 10 09/18/04





Michel Walsh said:
Hi,


SELECT a.[Date], a.Avail, a.OnOrder, a.Demand,
SUM(b.avail+b.OnOrder-b.Demand)

FROM myTable As a INNER JOIN myTable As b
ON a.[date] >= b.[date]



where "b" is concerned about records relative to operation occurring
before, or "at" the same moment than "a", so, it is then a matter to SUM
these.




Hoping it may help,
Vanderghast, Access MVP



Bernie said:
I have a continuous form. I would like to establish a
field either in the query or form text box to obtain a
quantity value from the previous record. Ex:

Rec Date Avail On Order Demand Balance
1 8/28 100 200 50 250
I would then like the Balance from Rec 1 to be set as the
Avail balance for the next record and continue this:
2 9/05 250 100 50 300
3 9/12 300 50 100 250

Bernie
Click to expand...
Click to expand...
Click to expand...
 
Hi,


With less typo, that makes:

SELECT a.lot,
LAST(a.processingTime),
#09/01/2004# + SUM( Nz(b.processingTime, 0) )
FROM myTable As a LEFT JOIN myTable as b
ON a.Lot > b.Lot
GROUP BY a.lot



Vanderghast, Access MVP
 
Back
Top