how do i sum a record and the record above it. in a new field?

I would like to perform subtracton in one field where it subtracts the data
in one record from the record above it. then display the results in a new
field.

please help.

matt

Here's one way using the Dlookup() function adapted from a sample db
downloaded from MS website. It sorts on tblMileage.id.

SELECT tblMileage.id, tblMileage.Date, tblMileage.Odometer,
DLookUp("[Odometer]","tblMileage","[ID] = " & [ID]-1) AS [Previous
Odometer], (tblMileage.Odometer - [Previous Odometer]) As Difference
FROM tblMileage;

Brian


"Matt Moses" Matt wrote in message
...
I would like to perform subtracton in one field where it subtracts the
data
in one record from the record above it. then display the results in a new
field.

please help.

matt

Here's one way using the Dlookup() function adapted from a sample db
downloaded from MS website. It sorts on tblMileage.id.

SELECT tblMileage.id, tblMileage.Date, tblMileage.Odometer,
DLookUp("[Odometer]","tblMileage","[ID] = " & [ID]-1) AS [Previous
Odometer], (tblMileage.Odometer - [Previous Odometer]) As Difference
FROM tblMileage;

Brian


"Matt Moses" Matt wrote in message
...
I would like to perform subtracton in one field where it subtracts the
data
in one record from the record above it. then display the results in a new
field.

please help.

matt

Matt:

Its important to realize that tables are sets and as such have no intrinsic
order, so concepts like 'first record', 'last record', 'previous record' have
no real meaning. Rows in a table can of course have an order imposed upon
them by means of some value or values in the rows. A table of financial
transactions might have a TransactionDateTime column for instance which has a
unique date/time value for each transaction record. Consequently the rows
can be ordered by TransactionDateTime. This sort of data is often inserted
automatically into a field by setting its DefaultValue property to Now() in
the table design.

The other important point is that the value you are aiming at can be
computed from the existing data, so should not be stored in a field in the
table, as that would introduce redundancy and leave the door open to update
anomalies, but computed as and when required. This is usually done as a
computed column in a query.

Its not absolutely clear what you are trying to do but it sounds rather like
you want to compute a balance. Say you have a table of financial
transactions where credits are positive values and debits negative values in
an Amount column, to compute the running balance you'd Sum all the Amounts up
to and including the current transaction. This can be done in a subquery
like so:

SELECT TransactionDateTime, Amount,
(SELECT SUM(Amount)
FROM Transactions AS T2
WHERE T2.TransactionDateTime = T1.TransactionDateTime) AS Balance
FROM Transactions AS T1
ORDER BY TransactionDateTime;

The two instances of the Transactions table here are distinguished by the
aliases T1 and T2, which enables the subquery to be correlated with the outer
query.

The above query would not be updatable however as it includes the SUM
aggregate function. In Access the use of any SQL aggregate function in a
query renders it read only. To make it updatable you'd have to use the VBA
DSum function like so:

SELECT TransactionDateTime, Amount,
DSum("Amount","Transactions", "TransactionDateTime = #"
& Format([TransactionDateTime],"mm/dd/yyyy hh:nn:ss" & "#") AS Balance
FROM Transactions
ORDER BY TransactionDateTime;

The Format function here is used to internationalize the query as literal
date/time values in Access must be in US date format or otherwise
internationally unambiguous.

The above probably doesn't apply precisely to your table, but I hope it
gives you a general idea of how this sort of thing is done. If you need
further advice on applying this to your table post back with more detailed
information on the field sin question and just what you want to achieve.

Ken Sheridan
Stafford, England

"Matt Moses" wrote:

I would like to perform subtracton in one field where it subtracts the data
in one record from the record above it. then display the results in a new
field.

please help.

matt

Matt:

Its important to realize that tables are sets and as such have no intrinsic
order, so concepts like 'first record', 'last record', 'previous record' have
no real meaning. Rows in a table can of course have an order imposed upon
them by means of some value or values in the rows. A table of financial
transactions might have a TransactionDateTime column for instance which has a
unique date/time value for each transaction record. Consequently the rows
can be ordered by TransactionDateTime. This sort of data is often inserted
automatically into a field by setting its DefaultValue property to Now() in
the table design.

The other important point is that the value you are aiming at can be
computed from the existing data, so should not be stored in a field in the
table, as that would introduce redundancy and leave the door open to update
anomalies, but computed as and when required. This is usually done as a
computed column in a query.

Its not absolutely clear what you are trying to do but it sounds rather like
you want to compute a balance. Say you have a table of financial
transactions where credits are positive values and debits negative values in
an Amount column, to compute the running balance you'd Sum all the Amounts up
to and including the current transaction. This can be done in a subquery
like so:

SELECT TransactionDateTime, Amount,
(SELECT SUM(Amount)
FROM Transactions AS T2
WHERE T2.TransactionDateTime = T1.TransactionDateTime) AS Balance
FROM Transactions AS T1
ORDER BY TransactionDateTime;

The two instances of the Transactions table here are distinguished by the
aliases T1 and T2, which enables the subquery to be correlated with the outer
query.

The above query would not be updatable however as it includes the SUM
aggregate function. In Access the use of any SQL aggregate function in a
query renders it read only. To make it updatable you'd have to use the VBA
DSum function like so:

SELECT TransactionDateTime, Amount,
DSum("Amount","Transactions", "TransactionDateTime = #"
& Format([TransactionDateTime],"mm/dd/yyyy hh:nn:ss" & "#") AS Balance
FROM Transactions
ORDER BY TransactionDateTime;

The Format function here is used to internationalize the query as literal
date/time values in Access must be in US date format or otherwise
internationally unambiguous.

The above probably doesn't apply precisely to your table, but I hope it
gives you a general idea of how this sort of thing is done. If you need
further advice on applying this to your table post back with more detailed
information on the field sin question and just what you want to achieve.

Ken Sheridan
Stafford, England

"Matt Moses" wrote:

I would like to perform subtracton in one field where it subtracts the data
in one record from the record above it. then display the results in a new
field.

please help.

matt