I need help

B

bill

I can't manage this: I have a table with one column with five records.
e.g. A1,A2,A3,A4,A5 I want a function to calculate A5-A4, A4-A3, A3-A2, A2-A1
Bill
 
J

John Vinson

I can't manage this: I have a table with one column with five records.
e.g. A1,A2,A3,A4,A5 I want a function to calculate A5-A4, A4-A3, A3-A2, A2-A1
Bill
Click to expand...

You're treating an Access relational table as if it were an Excel
spreadsheet. It isn't.

Unlike a spreadsheet, a Table HAS NO ORDER. There *is* no row 1, row
2, row 3 in any usable manner - there is in fact no row-number field.
A Table should be considered an unordered "heap" of data.

If your table has some other field which defines the order of records,
you can use a Self Join query to link the table to itself, using
criteria on this order field to link each record to the preceding
record; or, you could export the table to Excel, where this process is
very easy (you've done it yourself in your question in fact).

John W. Vinson[MVP]
 
B

bill

Thanks for your reply...
My table has a field with dates which defines the order of records.
How can I link each record to the preceding
record; Give me an example.
Thanks again.
 
J

John Vinson

Thanks for your reply...
My table has a field with dates which defines the order of records.
How can I link each record to the preceding
record; Give me an example.
Thanks again.
Click to expand...

Try creating a query. Add your table to the query TWICE - Access will
alias the second instance by appending _1 to its name. Don't use any
join line.

Select the date field from table_1; on the Criteria line under it put

=(SELECT Max([datefield]) FROM table AS X WHERE X.datefield <
table.datefield)

This will give you the fields in
for all dates, and - except
for the first in a date series - the fields for the most recent
previous date in table_1. You can then use an expression such as

ChangeSinceLast:
.[fieldname] - [table_1].[fieldname]

to calculate the change from record to record.

John W. Vinson[MVP]
 
Top