Show value of previous record in current record*1262008

[J.Alladien]

Hello All,

I have a table and I'd like it to Show value of previous record in current
record f.e

ID amnt prv amnt
1 100 100
2 234 100
3 512 234
4 67 512

etc etc, is this possible?

Thanks in advance!

 

[John W. Vinson]

Hello All,

I have a table and I'd like it to Show value of previous record in current
record f.e

ID amnt prv amnt
1 100 100
2 234 100
3 512 234
4 67 512

etc etc, is this possible?

Thanks in advance!

Just for clarity: what do you mean by "the previous record"? Access Tables are
NOT like spreadsheets; there is no inherent order to the records. In addition,
if the ID is an Autonumber, you're not guaranteed to have sequential numbers.

That said... your table should *not* contain the [prv amnt] as a field. If you
store a number in record 4, and subsequently edit the Amt in record 3, the
[Prv amnt] field in record 4 will now be WRONG with no automatic way to detect
the error. Instead, use a Query to look up the previous amount. A Subquery
will do this; create a new query, select View... SQL, and copy and paste this
into the SQL window:

SELECT ID, [Amnt], (SELECT X.[Amnt] FROM yourtable AS X WHERE X.ID = (SELECT
Max(Y.[ID]) FROM yourtable AS Y WHERE Y.ID < X.ID)) AS [Prv Amnt]);

 

[MikeJohnB]

Where are you trying to do this? A field in a table? Dont. it doesn't make
sense to do that.

In a Control on a form?

Place an unbound Text Box Control on the Form and for its Control source
type:

=DLast("[amnt]","[YourTableName Here]")

Hope this helps???

Regards

 

[J.Alladien]

Hi John,

When I applied as follows I get foll error "reserved error (-3025) there is
no message for this error"
here is the SQL

SELECT ID, [Amnt], (SELECT X.[Amnt] FROM Table1 AS X WHERE X.ID = (SELECT
Max(Y.[ID]) FROM Table1 AS Y WHERE Y.ID < X.ID)) AS [Prv Amnt];

Or am I missing somethin here?

Hello All,

I have a table and I'd like it to Show value of previous record in current
record f.e

ID amnt prv amnt
1 100 100
2 234 100
3 512 234
4 67 512

etc etc, is this possible?

Thanks in advance!

Just for clarity: what do you mean by "the previous record"? Access Tables are
NOT like spreadsheets; there is no inherent order to the records. In addition,
if the ID is an Autonumber, you're not guaranteed to have sequential numbers.

That said... your table should *not* contain the [prv amnt] as a field. If you
store a number in record 4, and subsequently edit the Amt in record 3, the
[Prv amnt] field in record 4 will now be WRONG with no automatic way to detect
the error. Instead, use a Query to look up the previous amount. A Subquery
will do this; create a new query, select View... SQL, and copy and paste this
into the SQL window:

SELECT ID, [Amnt], (SELECT X.[Amnt] FROM yourtable AS X WHERE X.ID = (SELECT
Max(Y.[ID]) FROM yourtable AS Y WHERE Y.ID < X.ID)) AS [Prv Amnt]);

 

[Douglas J. Steele]

I think John may have left out the FROM clause of the query.

Try:

SELECT ID, [Amnt], (SELECT X.[Amnt] FROM Table1 AS X WHERE X.ID = (SELECT
Max(Y.[ID]) FROM Table1 AS Y WHERE Y.ID < X.ID)) AS [Prv Amnt]
FROM Table1


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi John,

When I applied as follows I get foll error "reserved error (-3025) there
is
no message for this error"
here is the SQL

SELECT ID, [Amnt], (SELECT X.[Amnt] FROM Table1 AS X WHERE X.ID = (SELECT
Max(Y.[ID]) FROM Table1 AS Y WHERE Y.ID < X.ID)) AS [Prv Amnt];

Or am I missing somethin here?

Hello All,

I have a table and I'd like it to Show value of previous record in
current
record f.e

ID amnt prv amnt
1 100 100
2 234 100
3 512 234
4 67 512

etc etc, is this possible?

Thanks in advance!

Just for clarity: what do you mean by "the previous record"? Access
Tables are
NOT like spreadsheets; there is no inherent order to the records. In
addition,
if the ID is an Autonumber, you're not guaranteed to have sequential
numbers.

That said... your table should *not* contain the [prv amnt] as a field.
If you
store a number in record 4, and subsequently edit the Amt in record 3,
the
[Prv amnt] field in record 4 will now be WRONG with no automatic way to
detect
the error. Instead, use a Query to look up the previous amount. A
Subquery
will do this; create a new query, select View... SQL, and copy and paste
this
into the SQL window:

SELECT ID, [Amnt], (SELECT X.[Amnt] FROM yourtable AS X WHERE X.ID =
(SELECT
Max(Y.[ID]) FROM yourtable AS Y WHERE Y.ID < X.ID)) AS [Prv Amnt]);

 

[J.Alladien]

Hi Doug,

I copied it and when I run it the query opens up fine and everything but
"Prv Amnt" goes blank(empty)!

I think John may have left out the FROM clause of the query.

Try:

SELECT ID, [Amnt], (SELECT X.[Amnt] FROM Table1 AS X WHERE X.ID = (SELECT
Max(Y.[ID]) FROM Table1 AS Y WHERE Y.ID < X.ID)) AS [Prv Amnt]
FROM Table1


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi John,

When I applied as follows I get foll error "reserved error (-3025) there
is
no message for this error"
here is the SQL

SELECT ID, [Amnt], (SELECT X.[Amnt] FROM Table1 AS X WHERE X.ID = (SELECT
Max(Y.[ID]) FROM Table1 AS Y WHERE Y.ID < X.ID)) AS [Prv Amnt];

Or am I missing somethin here?

On Sat, 6 Dec 2008 13:00:01 -0800, J.Alladien

Hello All,

I have a table and I'd like it to Show value of previous record in
current
record f.e

ID amnt prv amnt
1 100 100
2 234 100
3 512 234
4 67 512

etc etc, is this possible?

Thanks in advance!

Just for clarity: what do you mean by "the previous record"? Access
Tables are
NOT like spreadsheets; there is no inherent order to the records. In
addition,
if the ID is an Autonumber, you're not guaranteed to have sequential
numbers.

That said... your table should *not* contain the [prv amnt] as a field.
If you
store a number in record 4, and subsequently edit the Amt in record 3,
the
[Prv amnt] field in record 4 will now be WRONG with no automatic way to
detect
the error. Instead, use a Query to look up the previous amount. A
Subquery
will do this; create a new query, select View... SQL, and copy and paste
this
into the SQL window:

SELECT ID, [Amnt], (SELECT X.[Amnt] FROM yourtable AS X WHERE X.ID =
(SELECT
Max(Y.[ID]) FROM yourtable AS Y WHERE Y.ID < X.ID)) AS [Prv Amnt]);