Previous record in Query

[Andy]

Hello,

I have query table wherein it has some calaulated fields. Some of it as
follow_

Tonnage Length FromSta ToSta
500 400 1200 1600
300 240 1600 1840
350 270 1840 2110

Length, FromSta & To Sta are calculated fields. How do I get 1600 value in
2nd record of FromSta field from the 1st record of ToSta field and like wise?

Andy

 

[Michel Walsh]

I assume toSta is given, NOT computed.

For the first record, fromSta should also be given.

Something like:


Tonnage Length FromSta ToSta
500 400 1200 1600
300 240 NULL 1840
350 270 NULL 2110


Then, the FromSta values can be determined with a query like:



SELECT a.tonnage, a.length, NZ(MAX(b.toSta), a.fromSta) AS computedFromSta,
a.toSta
FROM tableNameHere AS a LEFT JOIN tableNameHere AS b
ON a.toSta > b.toSta
GROUP BY a.tonnage, a.length, a.toSta
ORDER BY a.toSta




The ORDER BY clause is not necessary, but useful for checking.




Hoping it may help,
Vanderghast, Access MVP

 

[Andy]

Michel, Thank you.

Actualy ToSta iand FromSta both are computed field. What i need to do is to
calculate ToSta based on FromSta value with condition that If I ented value
in FromSta then ToSta shall take that value and calculateits value and place
that value in the record of that field as well as it place that value in NEXT
RECORD AS VALUE IN FromSta.

Is it possible?

 

[Michel Walsh]

As I understand it, no, since a record can have neither FromSta neither
ToSta, right? If so, that record is 'lost', unless you have ANOTHER FIELD
allowing to say where that record should be in relation to the other
records.

In a table of a database, records have no fixed position, in themselves.
They can move around, as, in example, when a 'data page split' occurs. On
the other hand, it the record ows a field like a date_time_stamp, or an
autonumber increasing sequentially without duplicated value, such field can
effectively 'rank' the records among each others.


If either FromSta either ToSta is supplied, for any given record, then we
may be able to order, rank, the records accordingly to what is supplied.

---untested---

SELECT a.tonnage, a.length,
LAST(Nz( a.fromSta, Nz(MAX(b.toSta), MAX(b.fromSta)))) AS
computedFromSta,
LAST(Nz( a.toSta, Nz(MIN(c.toSta), MIN(c.fromSta)))) AS computedToSta

FROM (myTable AS a LEFT JOIN myTable As b
ON Nz(a.FromSta, a.ToSta) > Nz(b.FromSta), b.ToSta) )
LEFT JOIN myTable AS c
ON Nz(a.FromSta, a.ToSta) < Nz(c.FromSta), c.ToSta) )


GROUP BY a.tonnage, a.length, Nz( a.fromSta, a.toSta)




The logic behind is to get, under the alias b, all the records having
{fromSta, toSta} lower, so, before, the ones with alias a. The MAX value
is thus the 'preceding' one. For alias c, the records with {fromSta, toSta}
are those with an higher value, so after, the ones with alias a. So,
MIN(c.xxx) is the value closest 'succeeding' one. Since either fromSta,
either toSta can be null, we play a lor with Nz to circum-navigate that
indetermination... but again, at least ONE of the value, fromSta, or toSta,
must be given, in any record.




Hoping it may help,
Vanderghast, Access MVP

 

[Andy]

Hi Michel,
I am puiitng my SQL statement here so that will easy for you to understand
what i need do. Cause this is beyound my capasity!!!!!
SELECT Table1.ID, DWPRecord.Date, DWPRecord.ItemNo, ItemList.ItemName,
Table1.Lot, Table1.[S-Lot], Table1.HMAinSubLot, (SELECT SUM([HMAinSubLot])
FROM Table1 As A WHERE A.[Date]<=Table1.[Date] AND A.[ID]<=Table1.[ID] AND
A.[ItemNo]=Table1.[ItemNo]) AS TotalHMA, -Int(-[TotalHMA]/5000) AS LotNo,
-Int(-[TotalHMA]/500) AS SubLotNo, Table1.Hwy, Table1.Dir, Table1.Lane,
Table1.Lift, Table1.RandomNo, Table1.RandomX, Table1.RandomY,
([HMAinSubLot]*[RandomNo]+([TotalHma]-[HMAinSubLot])) AS PST,
[PST]-(Nz((SELECT Sum([HMAinSubLot]) FROM Table1 As A WHERE
A.[Date]<Table1.[Date] AND A.[ID]<=Table1.[ID] AND
A.[ItemNo]=Table1.[ItemNo]),0)) AS PSToDayT,
[FrSta]+([PSToDayT]/([P-W]*[I-BRD]*([P-D]/1000))) AS PSSta,
DWPRecord.[I-BRD], DWPRecord.[P-W], DWPRecord.[P-D],
([TotalHMA]/([P-W]*([P-D]/1000)*[I-BRD])) AS LC, Table1.FrSta, [FrSta]+[LC]
AS ToSta, ([LC]*[RandomX])+[Table1].[FrSta] AS CoreSta, [P-W]*[Randomy] AS
CoreOffset
FROM ItemList INNER JOIN (DWPRecord INNER JOIN Table1 ON DWPRecord.DWID =
Table1.ID) ON ItemList.ItemID = DWPRecord.ItemNo;


This query works good if road has only one lane. But highways has more then
one lane. So If I change road lane or dirrection then my tosta calculation
becomes wrong. Thats why I need to make toSta to become FrSta so if I change
Lane then it worls ok.
Andy

 

[Michel Walsh]

Consider that query as if it was a table.

What could be the ORDER BY statement that will define, uniquely, the way
records should be sequenced? that will present the wanted 'first' record to
always appear first; the second, second, etc...

As example, does

ORDER BY Table1.ID, DWPRecord.Date, ItemList.ItemID


would be fine?


If so, you can then relay on that fact to retrieve the 'previous' record.


SELECT a.id, a.date, a.itemID, ...

FROM yourQuery AS a LEFT JOIN yourQuery AS b
ON (a.id > b.id OR (
a.id=b.id AND (
a.date > b.date OR (
a.date = b.date AND (
a.itemID > b.itemID )))))

GROUP BY a.id, a.date, a.itemID



is the starting point. In the SELECT statement, consider now that b refers
to all possible records occurring before the record with the values {a.id,
a.date, a.itemID}. MAX(b.toSta), as example, will produce the maximum value
for toSta, among those b-records.



SELECT a.id, a.date, a.itemID, NZ( a.fromSta, MAX(b.toSta))

FROM yourQuery AS a LEFT JOIN yourQuery AS b
ON (a.id > b.id OR (
a.id=b.id AND (
a.date > b.date OR (
a.date = b.date AND (
a.itemID > b.itemID )))))

GROUP BY a.id, a.date, a.itemID



thus replaces a potential NULL value for a.fromSta with the MAX(b.toSta).



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,
I am puiitng my SQL statement here so that will easy for you to understand
what i need do. Cause this is beyound my capasity!!!!!
SELECT Table1.ID, DWPRecord.Date, DWPRecord.ItemNo, ItemList.ItemName,
Table1.Lot, Table1.[S-Lot], Table1.HMAinSubLot, (SELECT SUM([HMAinSubLot])
FROM Table1 As A WHERE A.[Date]<=Table1.[Date] AND A.[ID]<=Table1.[ID]
AND
A.[ItemNo]=Table1.[ItemNo]) AS TotalHMA, -Int(-[TotalHMA]/5000) AS LotNo,
-Int(-[TotalHMA]/500) AS SubLotNo, Table1.Hwy, Table1.Dir, Table1.Lane,
Table1.Lift, Table1.RandomNo, Table1.RandomX, Table1.RandomY,
([HMAinSubLot]*[RandomNo]+([TotalHma]-[HMAinSubLot])) AS PST,
[PST]-(Nz((SELECT Sum([HMAinSubLot]) FROM Table1 As A WHERE
A.[Date]<Table1.[Date] AND A.[ID]<=Table1.[ID] AND
A.[ItemNo]=Table1.[ItemNo]),0)) AS PSToDayT,
[FrSta]+([PSToDayT]/([P-W]*[I-BRD]*([P-D]/1000))) AS PSSta,
DWPRecord.[I-BRD], DWPRecord.[P-W], DWPRecord.[P-D],
([TotalHMA]/([P-W]*([P-D]/1000)*[I-BRD])) AS LC, Table1.FrSta,
[FrSta]+[LC]
AS ToSta, ([LC]*[RandomX])+[Table1].[FrSta] AS CoreSta, [P-W]*[Randomy] AS
CoreOffset
FROM ItemList INNER JOIN (DWPRecord INNER JOIN Table1 ON DWPRecord.DWID =
Table1.ID) ON ItemList.ItemID = DWPRecord.ItemNo;


This query works good if road has only one lane. But highways has more
then
one lane. So If I change road lane or dirrection then my tosta
calculation
becomes wrong. Thats why I need to make toSta to become FrSta so if I
change
Lane then it worls ok.
Andy

As I understand it, no, since a record can have neither FromSta neither
ToSta, right? If so, that record is 'lost', unless you have ANOTHER FIELD
allowing to say where that record should be in relation to the other
records.

In a table of a database, records have no fixed position, in themselves.
They can move around, as, in example, when a 'data page split' occurs. On
the other hand, it the record ows a field like a date_time_stamp, or an
autonumber increasing sequentially without duplicated value, such field
can
effectively 'rank' the records among each others.


If either FromSta either ToSta is supplied, for any given record, then we
may be able to order, rank, the records accordingly to what is supplied.

---untested---

SELECT a.tonnage, a.length,
LAST(Nz( a.fromSta, Nz(MAX(b.toSta), MAX(b.fromSta)))) AS
computedFromSta,
LAST(Nz( a.toSta, Nz(MIN(c.toSta), MIN(c.fromSta)))) AS computedToSta

FROM (myTable AS a LEFT JOIN myTable As b
ON Nz(a.FromSta, a.ToSta) > Nz(b.FromSta), b.ToSta) )
LEFT JOIN myTable AS c
ON Nz(a.FromSta, a.ToSta) < Nz(c.FromSta), c.ToSta) )


GROUP BY a.tonnage, a.length, Nz( a.fromSta, a.toSta)




The logic behind is to get, under the alias b, all the records having
{fromSta, toSta} lower, so, before, the ones with alias a. The MAX
value
is thus the 'preceding' one. For alias c, the records with {fromSta,
toSta}
are those with an higher value, so after, the ones with alias a. So,
MIN(c.xxx) is the value closest 'succeeding' one. Since either fromSta,
either toSta can be null, we play a lor with Nz to circum-navigate that
indetermination... but again, at least ONE of the value, fromSta, or
toSta,
must be given, in any record.




Hoping it may help,
Vanderghast, Access MVP

 

[Andy]

Michel,
Thank you very much.
I think you gave me enough education. Now I'll do some try to make it work.
Thank you again

Andy

Consider that query as if it was a table.

What could be the ORDER BY statement that will define, uniquely, the way
records should be sequenced? that will present the wanted 'first' record to
always appear first; the second, second, etc...

As example, does

ORDER BY Table1.ID, DWPRecord.Date, ItemList.ItemID


would be fine?


If so, you can then relay on that fact to retrieve the 'previous' record.


SELECT a.id, a.date, a.itemID, ...

FROM yourQuery AS a LEFT JOIN yourQuery AS b
ON (a.id > b.id OR (
a.id=b.id AND (
a.date > b.date OR (
a.date = b.date AND (
a.itemID > b.itemID )))))

GROUP BY a.id, a.date, a.itemID



is the starting point. In the SELECT statement, consider now that b refers
to all possible records occurring before the record with the values {a.id,
a.date, a.itemID}. MAX(b.toSta), as example, will produce the maximum value
for toSta, among those b-records.



SELECT a.id, a.date, a.itemID, NZ( a.fromSta, MAX(b.toSta))

FROM yourQuery AS a LEFT JOIN yourQuery AS b
ON (a.id > b.id OR (
a.id=b.id AND (
a.date > b.date OR (
a.date = b.date AND (
a.itemID > b.itemID )))))

GROUP BY a.id, a.date, a.itemID



thus replaces a potential NULL value for a.fromSta with the MAX(b.toSta).



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,
I am puiitng my SQL statement here so that will easy for you to understand
what i need do. Cause this is beyound my capasity!!!!!
SELECT Table1.ID, DWPRecord.Date, DWPRecord.ItemNo, ItemList.ItemName,
Table1.Lot, Table1.[S-Lot], Table1.HMAinSubLot, (SELECT SUM([HMAinSubLot])
FROM Table1 As A WHERE A.[Date]<=Table1.[Date] AND A.[ID]<=Table1.[ID]
AND
A.[ItemNo]=Table1.[ItemNo]) AS TotalHMA, -Int(-[TotalHMA]/5000) AS LotNo,
-Int(-[TotalHMA]/500) AS SubLotNo, Table1.Hwy, Table1.Dir, Table1.Lane,
Table1.Lift, Table1.RandomNo, Table1.RandomX, Table1.RandomY,
([HMAinSubLot]*[RandomNo]+([TotalHma]-[HMAinSubLot])) AS PST,
[PST]-(Nz((SELECT Sum([HMAinSubLot]) FROM Table1 As A WHERE
A.[Date]<Table1.[Date] AND A.[ID]<=Table1.[ID] AND
A.[ItemNo]=Table1.[ItemNo]),0)) AS PSToDayT,
[FrSta]+([PSToDayT]/([P-W]*[I-BRD]*([P-D]/1000))) AS PSSta,
DWPRecord.[I-BRD], DWPRecord.[P-W], DWPRecord.[P-D],
([TotalHMA]/([P-W]*([P-D]/1000)*[I-BRD])) AS LC, Table1.FrSta,
[FrSta]+[LC]
AS ToSta, ([LC]*[RandomX])+[Table1].[FrSta] AS CoreSta, [P-W]*[Randomy] AS
CoreOffset
FROM ItemList INNER JOIN (DWPRecord INNER JOIN Table1 ON DWPRecord.DWID =
Table1.ID) ON ItemList.ItemID = DWPRecord.ItemNo;


This query works good if road has only one lane. But highways has more
then
one lane. So If I change road lane or dirrection then my tosta
calculation
becomes wrong. Thats why I need to make toSta to become FrSta so if I
change
Lane then it worls ok.
Andy

As I understand it, no, since a record can have neither FromSta neither
ToSta, right? If so, that record is 'lost', unless you have ANOTHER FIELD
allowing to say where that record should be in relation to the other
records.

In a table of a database, records have no fixed position, in themselves.
They can move around, as, in example, when a 'data page split' occurs. On
the other hand, it the record ows a field like a date_time_stamp, or an
autonumber increasing sequentially without duplicated value, such field
can
effectively 'rank' the records among each others.


If either FromSta either ToSta is supplied, for any given record, then we
may be able to order, rank, the records accordingly to what is supplied.

---untested---

SELECT a.tonnage, a.length,
LAST(Nz( a.fromSta, Nz(MAX(b.toSta), MAX(b.fromSta)))) AS
computedFromSta,
LAST(Nz( a.toSta, Nz(MIN(c.toSta), MIN(c.fromSta)))) AS computedToSta

FROM (myTable AS a LEFT JOIN myTable As b
ON Nz(a.FromSta, a.ToSta) > Nz(b.FromSta), b.ToSta) )
LEFT JOIN myTable AS c
ON Nz(a.FromSta, a.ToSta) < Nz(c.FromSta), c.ToSta) )


GROUP BY a.tonnage, a.length, Nz( a.fromSta, a.toSta)




The logic behind is to get, under the alias b, all the records having
{fromSta, toSta} lower, so, before, the ones with alias a. The MAX
value
is thus the 'preceding' one. For alias c, the records with {fromSta,
toSta}
are those with an higher value, so after, the ones with alias a. So,
MIN(c.xxx) is the value closest 'succeeding' one. Since either fromSta,
either toSta can be null, we play a lor with Nz to circum-navigate that
indetermination... but again, at least ONE of the value, fromSta, or
toSta,
must be given, in any record.




Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Small error in my last query, should be:

SELECT a.id, a.date, a.itemID, NZ( LAST(a.fromSta), MAX(b.toSta))

FROM yourQuery AS a LEFT JOIN yourQuery AS b
ON (a.id > b.id OR (
a.id=b.id AND (
a.date > b.date OR (
a.date = b.date AND (
a.itemID > b.itemID )))))

GROUP BY a.id, a.date, a.itemID



Indeed, since a.fromSta is not in the GROUP BY clause, it has to be
aggregated in the SELECT clause. That is an SQL-law.

Here, you can use LAST, FIRST, MIN, or MAX... it should be indifferent,
since {a.id, a.date, a.itemID} basically is defined from only ONE record,
so, LAST( a.field ) or MIN( a.field ) or MAX( a.field ) is, technically,
the same as a.field, in this case... but using an aggregate *is* required.



Vanderghast, Access MVP

 

[Andy]

Thanks Michel,
In this Query ' From yourQuery' means the Query which is current or other
Query Table? Cause I have only one Query based on Table1.
Andy

 

[Michel Walsh]

The query you posted. It is mentioned twice, in the query I proposed, but
see it as two references, two 'fingers', finger_a and finger_b, running
over the same list of data. Sure, the two 'fingers' could, in general, run
over different list of data, but here, they 'run' over the same list.



Vanderghast, Access MVP

 

[Andy]

Michel,
I tried the Query you suggested but it create an error 'Syntex error' .But
I could not figured out where is the problem?
I made Change in table1Query fields as_
Date ItemNo HMA TotalHMA Lot S-Lot Length StartSta FromSta ToSta
Lane
Covered
1/1/08 1 500 500 1 1 525 10000
10000 10525 1
1/1/08 1 300 800 1 2 315
?? 1
1/1/08 1 200 1000 1 2 210 10000
2
1/1/08 1 500 1500 1 3 525
2
I have formula in_
FromSta: IIF(NZ([StartSta]),[StartSta],??) ??-I need Previous value of
ToSta ie 10525
ToSta:[FromSta]+[LengthCovered] ie 10525+35=10840
StartSta is entered in table1
So when Lane changes I entered StartSta so FromSta takes this value and if
that field is empty then It bring Previous value of ToSta field
ANDY

 

[Michel Walsh]

Nz( argument1, argument2)


will return argument1, unless it is null, then, it will return argument2



So, is 'a' and 'b' are you two alias (fingers) to your table, try:


Nz( LAST(a.startSta), MAX(b.startSta) )



where LAST is just an aggregate to satisfy the GROUP BY requirements, can be
MIN, MAX, or FIRST, here.

The whole query can be:

-------------------------
SELECT a.id, a.date, a.itemID, NZ( LAST(a.fromSta), MAX(b.fromSta))

FROM yourQuery AS a LEFT JOIN yourQuery AS b
ON (a.id > b.id OR (
a.id=b.id AND (
a.date > b.date OR (
a.date = b.date AND (
a.itemID > b.itemID )))))

GROUP BY a.id, a.date, a.itemID
----------------------------



or, if you really don't like the aggregate LAST:

-------------------------
SELECT a.id, a.date, a.itemID, NZ(a.fromSta, MAX(b.fromSta))

FROM yourQuery AS a LEFT JOIN yourQuery AS b
ON (a.id > b.id OR (
a.id=b.id AND (
a.date > b.date OR (
a.date = b.date AND (
a.itemID > b.itemID )))))

GROUP BY a.id, a.date, a.itemID, a.fromSta
----------------------------


with a.fromSta now in the GROUP BY clause (so, no need to aggregate it in
the SELECT clause).




Hoping it may help,
Vanderghast, Access MVP

Michel,
I tried the Query you suggested but it create an error 'Syntex error' .But
I could not figured out where is the problem?
I made Change in table1Query fields as_
Date ItemNo HMA TotalHMA Lot S-Lot Length StartSta FromSta
ToSta
Lane
Covered
1/1/08 1 500 500 1 1 525 10000
10000 10525 1
1/1/08 1 300 800 1 2 315
?? 1
1/1/08 1 200 1000 1 2 210 10000
2
1/1/08 1 500 1500 1 3 525
2
I have formula in_
FromSta: IIF(NZ([StartSta]),[StartSta],??) ??-I need Previous value of
ToSta ie 10525
ToSta:[FromSta]+[LengthCovered] ie 10525+35=10840
StartSta is entered in table1
So when Lane changes I entered StartSta so FromSta takes this value and if
that field is empty then It bring Previous value of ToSta field
ANDY

The query you posted. It is mentioned twice, in the query I proposed, but
see it as two references, two 'fingers', finger_a and finger_b, running
over the same list of data. Sure, the two 'fingers' could, in general,
run
over different list of data, but here, they 'run' over the same list.



Vanderghast, Access MVP