Subtraction of values from 2 records of the same field

[Mishanya]

I have a query limited to show the 3 last records from table (by SQL
expression SELECT TOP 3 MyTable.Date):

ID Date FieldValue

I need to show a change in a FieldValue between any current and previous
records, so the row of report would include:

ID Date FieldValue(n) Change(FieldValue(n)-FieldValue(n-1))

1) How can I calculate such a thing (what formula should be put in unbound
box Change))?
2) In case of FieldValue(1) the FieldValue(n-1) does not appear in the query
result and should be pulled from the table?

 

[Wayne-I-M]

You can do it in the form or just base the form on a query.

Something like

SELECT TableName.ID, TableName.Date, TableName.FieldValue,
[TableName]![FieldValue]-DLookUp("[FieldValue]","TableName","[ID]=" & [ID]-1)
AS Change
FROM TableName;


Either method works fine if you ID is sequential

have a look at this for lots of methods

good luck

 

[Wayne-I-M]

ooops - forgot to put the link in

http://support.microsoft.com/kb/210504

sorry

 

[Mishanya]

Hi Wayne and thanks for Your responce.
The ID won't be necessarily running numbers - the records are dealing with
different clients, so for the same DateValue there will be more then one
record. In this case, I need the query to refer relatively to the previous
record from the queried selection and not from the table. So the ","[ID]=" &
[ID]-1) won't work.
I guess, I'll find more answers in the link you've attached.
Thanks again.

You can do it in the form or just base the form on a query.

Something like

SELECT TableName.ID, TableName.Date, TableName.FieldValue,
[TableName]![FieldValue]-DLookUp("[FieldValue]","TableName","[ID]=" & [ID]-1)
AS Change
FROM TableName;


Either method works fine if you ID is sequential

have a look at this for lots of methods

good luck

--
Wayne
Manchester, England.

I have a query limited to show the 3 last records from table (by SQL
expression SELECT TOP 3 MyTable.Date):

ID Date FieldValue

I need to show a change in a FieldValue between any current and previous
records, so the row of report would include:

ID Date FieldValue(n) Change(FieldValue(n)-FieldValue(n-1))

1) How can I calculate such a thing (what formula should be put in unbound
box Change))?
2) In case of FieldValue(1) the FieldValue(n-1) does not appear in the query
result and should be pulled from the table?

 

[Wayne-I-M]

Thats not a problem - have a look at allen bron's site for details of
methods. You want to use your select query that you have now (I assume) a
the sub and then just refer "upwards"

http://allenbrowne.com/subquery-01.html


--
Wayne
Manchester, England.

Hi Wayne and thanks for Your responce.
The ID won't be necessarily running numbers - the records are dealing with
different clients, so for the same DateValue there will be more then one
record. In this case, I need the query to refer relatively to the previous
record from the queried selection and not from the table. So the ","[ID]=" &
[ID]-1) won't work.
I guess, I'll find more answers in the link you've attached.
Thanks again.

You can do it in the form or just base the form on a query.

Something like

SELECT TableName.ID, TableName.Date, TableName.FieldValue,
[TableName]![FieldValue]-DLookUp("[FieldValue]","TableName","[ID]=" & [ID]-1)
AS Change
FROM TableName;


Either method works fine if you ID is sequential

have a look at this for lots of methods

good luck

--
Wayne
Manchester, England.

I have a query limited to show the 3 last records from table (by SQL
expression SELECT TOP 3 MyTable.Date):

ID Date FieldValue

I need to show a change in a FieldValue between any current and previous
records, so the row of report would include:

ID Date FieldValue(n) Change(FieldValue(n)-FieldValue(n-1))

1) How can I calculate such a thing (what formula should be put in unbound
box Change))?
2) In case of FieldValue(1) the FieldValue(n-1) does not appear in the query
result and should be pulled from the table?

 

[Mishanya]

Even better!
Thanks a lot!
ManUTD rules!

Thats not a problem - have a look at allen bron's site for details of
methods. You want to use your select query that you have now (I assume) a
the sub and then just refer "upwards"

http://allenbrowne.com/subquery-01.html


--
Wayne
Manchester, England.

Hi Wayne and thanks for Your responce.
The ID won't be necessarily running numbers - the records are dealing with
different clients, so for the same DateValue there will be more then one
record. In this case, I need the query to refer relatively to the previous
record from the queried selection and not from the table. So the ","[ID]=" &
[ID]-1) won't work.
I guess, I'll find more answers in the link you've attached.
Thanks again.

You can do it in the form or just base the form on a query.

Something like

SELECT TableName.ID, TableName.Date, TableName.FieldValue,
[TableName]![FieldValue]-DLookUp("[FieldValue]","TableName","[ID]=" & [ID]-1)
AS Change
FROM TableName;


Either method works fine if you ID is sequential

have a look at this for lots of methods

good luck

--
Wayne
Manchester, England.



:

I have a query limited to show the 3 last records from table (by SQL
expression SELECT TOP 3 MyTable.Date):

ID Date FieldValue

I need to show a change in a FieldValue between any current and previous
records, so the row of report would include:

ID Date FieldValue(n) Change(FieldValue(n)-FieldValue(n-1))

1) How can I calculate such a thing (what formula should be put in unbound
box Change))?
2) In case of FieldValue(1) the FieldValue(n-1) does not appear in the query
result and should be pulled from the table?