how to add to field from previous record

[Blondie]

if i want to update a value from a previous record by adding to it how would
i do that. If the cumulative value in the first record shows 100 kilometres
and the next record asks trip distance and i input 25 kilometres i want the
cumulative distance to update. is this possible. i've searched high and low
and i can't work out if this is possible. thank you to anybody who can be
of assistance

 

[Wayne-I-M]

Hi

You can code this - there are many examples on the web - or you can simply
use the DLookUp function

DLookUp will look at a record that is a specified number of records either
behind or front of your current record in the table.

You can refer to a previous record - in this case the preceeding one by
using -1 In the control source of a field in your current record. Of course
you "can" simply add the current record's number to the DLookUp field in the
same formula but I have found that it is useful to have a seperate unbound
field and refer to that - something like [UnBoundName] + [YourField]

In the case the UnBoundfield's record source would look like this.

=DLookUp("[FieldName]","TableName","[PrimaryField]=Forms![FormName]![ID]-1")

Change to what you normally use
FieldName
TabeName
PrimaryField
FormName

Hope this helps

 

[Larry Daugherty]

Hi Blondie,

The question in your post title doesn't really express your intent.
Achieving your intent as later clarified involves breaking at least
one rule for relational databases. "Wayne-I-M" has helped you to
do that.

The effective data rule goes something like this: "The content of
each field in a record is the result of the Primary Key of that record
alone and not the result of a calculation of any other data within
this database, so help me Codd". E.F. Codd and C.J. Date were the
fathers of SQL and DB2 (that little utility manufactured by Itty Bitty
Machines - otherwise known as IBM. I suggest that you view
www.mvps.orf/access and, in particular, read the Ten Commandments.

You've expressed what you want but in a relational database what you
want is illegal. The functionality you seek involves two levels: the
level of the data itself and the level of information about the data.
The data is in the record about *this* trip segment. That record
should store only things about *this* trip segment. You need another
level to show the total distance as the sum of all of the individual
distances. You could have an unbound textbox on your form and update
it in the OnChange event of the "segment distance" textbox.
Re-calculate information every time you need to display it, don't
store the results of calculations in tables. (There are some special
instances where there is a clear need to store calculated results,
such as Invoices... Your current situation doesn't involve one of
those special instances).. The same kind of thinking applies in
Reports but the Report designer gives you tools to address summing
issues directly.

For a working example of the results you might be seeking, go to
www.mapquest.com ask for travel directions and enter two geographic
points and look at the results returned.

Post back if you have issues in achieving a correct solution

HTH
--
-Larry-
--

Hi

You can code this - there are many examples on the web - or you can simply
use the DLookUp function

DLookUp will look at a record that is a specified number of records either
behind or front of your current record in the table.

You can refer to a previous record - in this case the preceeding one by
using -1 In the control source of a field in your current record. Of course
you "can" simply add the current record's number to the DLookUp field in the
same formula but I have found that it is useful to have a seperate unbound
field and refer to that - something like [UnBoundName] + [YourField]

In the case the UnBoundfield's record source would look like this.

=DLookUp("[FieldName]","TableName","[PrimaryField]=Forms![FormName]![I
D]-1")

Change to what you normally use
FieldName
TabeName
PrimaryField
FormName

Hope this helps
--
Wayne
Manchester, England.
Enjoy whatever it is you do

if i want to update a value from a previous record by adding to it how would
i do that. If the cumulative value in the first record shows 100 kilometres
and the next record asks trip distance and i input 25 kilometres i want the
cumulative distance to update. is this possible. i've searched high and low
and i can't work out if this is possible. thank you to anybody who can be
of assistance

 

[Wayne-I-M]

Hi Larry

Maybe I misunderstood - I can't see anything wrong with updateing a previous
record when data is added to the next.

eg.
25 cans of beans sold yesterday
2 can of beans added to an order
That mean we really sold 27 can of beans.

Or
25 cans of beans sold yesterday
2 can of beans added to an order
This could mean that we have 2 extra cans sold today

I'm confused
There is no problem and no rules are broken if a record is used to update a
previous record providing that the data is is linked to the primary of the
record being updated -

I'm still confused

Oh well - live goes on
--
Wayne
Manchester, England.
Enjoy whatever it is you do

Hi Blondie,

The question in your post title doesn't really express your intent.
Achieving your intent as later clarified involves breaking at least
one rule for relational databases. "Wayne-I-M" has helped you to
do that.

The effective data rule goes something like this: "The content of
each field in a record is the result of the Primary Key of that record
alone and not the result of a calculation of any other data within
this database, so help me Codd". E.F. Codd and C.J. Date were the
fathers of SQL and DB2 (that little utility manufactured by Itty Bitty
Machines - otherwise known as IBM. I suggest that you view
www.mvps.orf/access and, in particular, read the Ten Commandments.

You've expressed what you want but in a relational database what you
want is illegal. The functionality you seek involves two levels: the
level of the data itself and the level of information about the data.
The data is in the record about *this* trip segment. That record
should store only things about *this* trip segment. You need another
level to show the total distance as the sum of all of the individual
distances. You could have an unbound textbox on your form and update
it in the OnChange event of the "segment distance" textbox.
Re-calculate information every time you need to display it, don't
store the results of calculations in tables. (There are some special
instances where there is a clear need to store calculated results,
such as Invoices... Your current situation doesn't involve one of
those special instances).. The same kind of thinking applies in
Reports but the Report designer gives you tools to address summing
issues directly.

For a working example of the results you might be seeking, go to
www.mapquest.com ask for travel directions and enter two geographic
points and look at the results returned.

Post back if you have issues in achieving a correct solution

HTH
--
-Larry-
--

Hi

You can code this - there are many examples on the web - or you can simply
use the DLookUp function

DLookUp will look at a record that is a specified number of records either
behind or front of your current record in the table.

You can refer to a previous record - in this case the preceeding one by
using -1 In the control source of a field in your current record. Of course
you "can" simply add the current record's number to the DLookUp field in the
same formula but I have found that it is useful to have a seperate unbound
field and refer to that - something like [UnBoundName] + [YourField]

In the case the UnBoundfield's record source would look like this.

=DLookUp("[FieldName]","TableName","[PrimaryField]=Forms![FormName]![I
D]-1")

Change to what you normally use
FieldName
TabeName
PrimaryField
FormName

Hope this helps
--
Wayne
Manchester, England.
Enjoy whatever it is you do

if i want to update a value from a previous record by adding to it how would
i do that. If the cumulative value in the first record shows 100 kilometres
and the next record asks trip distance and i input 25 kilometres i want the
cumulative distance to update. is this possible. i've searched high and low
and i can't work out if this is possible. thank you to anybody who can be
of assistance

 

[Larry Daugherty]

Hi Wayne,

We may each have assumed different things about the initial records
discussed. They were not sufficiently clarified. I assumed records
at the same level; i.e. in the same recordset. You may well have
assumed something else.

If my assumption was right then your answer was wrong.

If you more correctly assumed that the two records under discussion
were not in the same recordset then your solution may have been
correct. In which case my apologies to you and OP.

The important thing is that OP eventually get a workable solution or
pointed in the right direction.

Regards,
--
-Larry-
--

Hi Larry

Maybe I misunderstood - I can't see anything wrong with updateing a previous
record when data is added to the next.

eg.
25 cans of beans sold yesterday
2 can of beans added to an order
That mean we really sold 27 can of beans.

Or
25 cans of beans sold yesterday
2 can of beans added to an order
This could mean that we have 2 extra cans sold today

I'm confused
There is no problem and no rules are broken if a record is used to update a
previous record providing that the data is is linked to the primary of the
record being updated -

I'm still confused

Oh well - live goes on
--
Wayne
Manchester, England.
Enjoy whatever it is you do

Hi Blondie,

The question in your post title doesn't really express your intent.
Achieving your intent as later clarified involves breaking at least
one rule for relational databases. "Wayne-I-M" has helped you to
do that.

The effective data rule goes something like this: "The content of
each field in a record is the result of the Primary Key of that record
alone and not the result of a calculation of any other data within
this database, so help me Codd". E.F. Codd and C.J. Date were the
fathers of SQL and DB2 (that little utility manufactured by Itty Bitty
Machines - otherwise known as IBM. I suggest that you view
www.mvps.orf/access and, in particular, read the Ten Commandments.

You've expressed what you want but in a relational database what you
want is illegal. The functionality you seek involves two levels: the
level of the data itself and the level of information about the data.
The data is in the record about *this* trip segment. That record
should store only things about *this* trip segment. You need another
level to show the total distance as the sum of all of the individual
distances. You could have an unbound textbox on your form and update
it in the OnChange event of the "segment distance" textbox.
Re-calculate information every time you need to display it, don't
store the results of calculations in tables. (There are some special
instances where there is a clear need to store calculated results,
such as Invoices... Your current situation doesn't involve one of
those special instances).. The same kind of thinking applies in
Reports but the Report designer gives you tools to address summing
issues directly.

For a working example of the results you might be seeking, go to
www.mapquest.com ask for travel directions and enter two geographic
points and look at the results returned.

Post back if you have issues in achieving a correct solution

HTH
--
-Larry-
--

Hi

You can code this - there are many examples on the web - or you

can
simply

use the DLookUp function

DLookUp will look at a record that is a specified number of

records
either

behind or front of your current record in the table.

You can refer to a previous record - in this case the preceeding

one
by

using -1 In the control source of a field in your current

record.
Of course

you "can" simply add the current record's number to the DLookUp field in the
same formula but I have found that it is useful to have a

seperate
unbound

field and refer to that - something like [UnBoundName] + [YourField]

In the case the UnBoundfield's record source would look like this.

=DLookUp("[FieldName]","TableName","[PrimaryField]=Forms![FormName]![I
D]-1")

Change to what you normally use
FieldName
TabeName
PrimaryField
FormName

Hope this helps
--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

if i want to update a value from a previous record by adding

to it
how would

i do that. If the cumulative value in the first record shows

100
kilometres

and the next record asks trip distance and i input 25

kilometres i
want the

cumulative distance to update. is this possible. i've searched high and low
and i can't work out if this is possible. thank you to

anybody
who can be

of assistance