Show value of previous record in current record*1262008

J

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!
 
J

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]);
 
M

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

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?



John W. Vinson said:
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]);
 
D

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)


J.Alladien said:
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?



John W. Vinson said:
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

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)!


Douglas J. Steele said:
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)


J.Alladien said:
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?



John W. Vinson said:
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]);
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top