Calculation Between Records

  • Thread starter Thread starter Spider
  • Start date Start date
S

Spider

Each record of the database has a field called "Mileage" that stores a
numeric value. What I would like to do is take the mileage from the previous
record and subtract it from the current record for each record entered. How
can I do this?
 
Spider said:
Each record of the database has a field called "Mileage" that stores a
numeric value. What I would like to do is take the mileage from the previous
record and subtract it from the current record for each record entered. How
can I do this?
Click to expand...


Use a subquery, or, if the query must be updatable, the DMax
function.

Without knowing the table, its fields and what "previous"
means (in terms of fields in each record), I don't think I
can be more specific.
 
Marshall said:
Use a subquery, or, if the query must be updatable, the DMax
function.

Without knowing the table, its fields and what "previous"
means (in terms of fields in each record), I don't think I
can be more specific.
Click to expand...


Private email from Spider:
Assume that the database has numerous records with only one field - Mileage.
Each day the vehicle mileage is entered into a new record. What I would like
to be able to do is display on a form the difference between the current
mileage & the previous days mileage for every entry.

Record 1 - Mileage = 500
Record 2 - Mileage = 750 The difference between record 2 & record 1 is 250.
This difference would be displayed on a form when the record is selected.
Record 3 - Mileage = 100 The difference between record 2 & record 1 is 250.
This difference would be displayed on a form when the record is selected.

What I can't figure out is how do I get the value from a previous record so
I can use it in the current record? I would like the mileage difference to
be calculated automatically each time the user enters the Mileage into the
new days (current) record.
Click to expand...


Please keep the correspondence in the newsgroups. The delay
in this response is because I don't check that account all
that often and is another good reason to stick to the
newsgroups.

You still haven't defined (in terms of fields in the table)
what "previous" means. Relational databases have no
inherent order to the records, including the order they were
entered. You may have some kind of special situation such
as the mileage number always increases, but your example
does not demonstrate it, probably because of a typo, but I
can't tell for sure. The most common way of determining
"previous" is by having a date field so the order of the
records can be easily determined.

You also failed to mention whether there is a single vehicle
or multiple vehicles. This information is critical to
working out the details of a query to get the value you
want.

Without those details, all I can do is provide an example
based on the extraordinarily simple assumptions you posted:

SELECT T.mileage,
T.mileage - (SELECT Max(X.mileage)
FROM thetable As X
WHERE X.mileage < T.mileage
) As Difference
FROM thetable As T
ORDER BY T.mileage
 
You are correct in assuming the Date field determines the order of the
records. I apologize for the confusion. There could easily be more than one
vehicle to track. Currently there is only one. Each vehicle is numbered and
that number is entered into each record.



Does this give you enough to work with?
 
Yes, that's the core of the requirements. I'm sorry if I
seem to be nagging on you, but, for future reference, it
really helps us answer questions if we have a real rather
than oversimplified abstraction of the problem. Another
important bit of information is a list of the pertinate
table fields and their data type. If there are multiple
tables involved, the relationship primary and foreign key
fields are also important.

Lecture's over, here's an air code example of that type of
query:

SELECT T.vehicleID, T.readdate, T.mileage,
T.mileage - (SELECT TOP 1 X.mileage
FROM thetable As X
WHERE X.vehicleID = T.vehicleID
AND X.readdate < T.readdate
ORDER BY X.readdate DESC
) As Difference
FROM thetable As T
ORDER BY T.vehicleID, T.mileage
 
Thank you for your help. I'm somewhat new with SQL but I'll give it a go and
see what happens. I'll try to be more informative in the future. :-)

One other thing...can this code be placed in a table, or do I use it with a
query?

Marshall Barton said:
Yes, that's the core of the requirements. I'm sorry if I
seem to be nagging on you, but, for future reference, it
really helps us answer questions if we have a real rather
than oversimplified abstraction of the problem. Another
important bit of information is a list of the pertinate
table fields and their data type. If there are multiple
tables involved, the relationship primary and foreign key
fields are also important.

Lecture's over, here's an air code example of that type of
query:

SELECT T.vehicleID, T.readdate, T.mileage,
T.mileage - (SELECT TOP 1 X.mileage
FROM thetable As X
WHERE X.vehicleID = T.vehicleID
AND X.readdate < T.readdate
ORDER BY X.readdate DESC
) As Difference
FROM thetable As T
ORDER BY T.vehicleID, T.mileage
--
Marsh
MVP [MS Access]


Spider said:
You are correct in assuming the Date field determines the order of the
records. I apologize for the confusion. There could easily be more than
one
vehicle to track. Currently there is only one. Each vehicle is numbered
and
that number is entered into each record.

Does this give you enough to work with?
Click to expand...
Click to expand...
 
An SQL statement is the definition of a query, so it can
only be used as a query. Furthermore, the Normalization
rules of Relational databases (not just Access) state that
calculated values such as this should **not** be saved to a
table. If they were and you ever edited a mileage reading
to correct a typo or whatever, the saved calculation would
be incorrect. Therefore, you should (re)do the calculation
whenever you need the value.

Try studying the Access Help chapter (Contents) - Creating
and Working with Databases - About designing a database.
One way I like to summarize those rules is -
If you ever need to change a value anywhere
in any record in any table, you need to be able
to do it by only changing one column, in one row,
in one table.
If you think about that, you'll see that a calculated value
would have to be changed, whenever any of the fields used in
the calulation were changed, so saving calculated values are
a no-no.

Don't forget that all data stored in tables can **only** be
retrieved using a query. This means that queries are an
essential part of every database project. On the other
hand, a query can be used in many different contexts such as
displaying data in a query window (primarily for debugging
purposes), as the Record Source for a form or report, as the
Row Source for the combo box and list box controls, in a VBA
procedure to open a Recordset as well as other situations
that retrieve data. Even when you display a table in sheet
view, access uses an internally created query to do the
work.
--
Marsh
MVP [MS Access]


Spider said:
Thank you for your help. I'm somewhat new with SQL but I'll give it a go and
see what happens. I'll try to be more informative in the future. :-)

One other thing...can this code be placed in a table, or do I use it with a
query?


Yes, that's the core of the requirements. I'm sorry if I
seem to be nagging on you, but, for future reference, it
really helps us answer questions if we have a real rather
than oversimplified abstraction of the problem. Another
important bit of information is a list of the pertinate
table fields and their data type. If there are multiple
tables involved, the relationship primary and foreign key
fields are also important.

Lecture's over, here's an air code example of that type of
query:

SELECT T.vehicleID, T.readdate, T.mileage,
T.mileage - (SELECT TOP 1 X.mileage
FROM thetable As X
WHERE X.vehicleID = T.vehicleID
AND X.readdate < T.readdate
ORDER BY X.readdate DESC
) As Difference
FROM thetable As T
ORDER BY T.vehicleID, T.mileage
--
Marsh
MVP [MS Access]


Spider said:
You are correct in assuming the Date field determines the order of the
records. I apologize for the confusion. There could easily be more than
one
vehicle to track. Currently there is only one. Each vehicle is numbered
and
that number is entered into each record.

Does this give you enough to work with?


Each record of the database has a field called "Mileage" that stores a
numeric value. What I would like to do is take the mileage from the
previous
record and subtract it from the current record for each record entered.
How
can I do this?


Marshall Barton wrote:
Use a subquery, or, if the query must be updatable, the DMax
function.

Without knowing the table, its fields and what "previous"
means (in terms of fields in each record), I don't think I
can be more specific.


Private email from Spider:
Assume that the database has numerous records with only one field -
Mileage.
Each day the vehicle mileage is entered into a new record. What I would
like
to be able to do is display on a form the difference between the current
mileage & the previous days mileage for every entry.

Record 1 - Mileage = 500
Record 2 - Mileage = 750 The difference between record 2 & record 1 is
250.
This difference would be displayed on a form when the record is
selected.
Record 3 - Mileage = 100 The difference between record 2 & record 1 is
250.
This difference would be displayed on a form when the record is
selected.

What I can't figure out is how do I get the value from a previous record
so
I can use it in the current record? I would like the mileage difference
to
be calculated automatically each time the user enters the Mileage into
the
new days (current) record.


"Marshall Barton" wrote
Please keep the correspondence in the newsgroups. The delay
in this response is because I don't check that account all
that often and is another good reason to stick to the
newsgroups.

You still haven't defined (in terms of fields in the table)
what "previous" means. Relational databases have no
inherent order to the records, including the order they were
entered. You may have some kind of special situation such
as the mileage number always increases, but your example
does not demonstrate it, probably because of a typo, but I
can't tell for sure. The most common way of determining
"previous" is by having a date field so the order of the
records can be easily determined.

You also failed to mention whether there is a single vehicle
or multiple vehicles. This information is critical to
working out the details of a query to get the value you
want.

Without those details, all I can do is provide an example
based on the extraordinarily simple assumptions you posted:

SELECT T.mileage,
T.mileage - (SELECT Max(X.mileage)
FROM thetable As X
WHERE X.mileage < T.mileage
) As Difference
FROM thetable As T
ORDER BY T.mileage
Click to expand...
Click to expand...
Click to expand...
 
You mention using a combo box with a query. I have several on the form, but
I do not get the results I expected. I have set the record source to a query
that includes all the tables in the database. The control source is set to a
field found in the query. The row source type is Table/Query. The bound
column is 1 (this doesn't seem to make any difference what value I use). Two
of the combo boxes display nothing, and one displays the Auto Number values.
Can you tell me what I'm doing wrong?

Marshall Barton said:
An SQL statement is the definition of a query, so it can
only be used as a query. Furthermore, the Normalization
rules of Relational databases (not just Access) state that
calculated values such as this should **not** be saved to a
table. If they were and you ever edited a mileage reading
to correct a typo or whatever, the saved calculation would
be incorrect. Therefore, you should (re)do the calculation
whenever you need the value.

Try studying the Access Help chapter (Contents) - Creating
and Working with Databases - About designing a database.
One way I like to summarize those rules is -
If you ever need to change a value anywhere
in any record in any table, you need to be able
to do it by only changing one column, in one row,
in one table.
If you think about that, you'll see that a calculated value
would have to be changed, whenever any of the fields used in
the calulation were changed, so saving calculated values are
a no-no.

Don't forget that all data stored in tables can **only** be
retrieved using a query. This means that queries are an
essential part of every database project. On the other
hand, a query can be used in many different contexts such as
displaying data in a query window (primarily for debugging
purposes), as the Record Source for a form or report, as the
Row Source for the combo box and list box controls, in a VBA
procedure to open a Recordset as well as other situations
that retrieve data. Even when you display a table in sheet
view, access uses an internally created query to do the
work.
--
Marsh
MVP [MS Access]


Spider said:
Thank you for your help. I'm somewhat new with SQL but I'll give it a go
and
see what happens. I'll try to be more informative in the future. :-)

One other thing...can this code be placed in a table, or do I use it with
a
query?


Yes, that's the core of the requirements. I'm sorry if I
seem to be nagging on you, but, for future reference, it
really helps us answer questions if we have a real rather
than oversimplified abstraction of the problem. Another
important bit of information is a list of the pertinate
table fields and their data type. If there are multiple
tables involved, the relationship primary and foreign key
fields are also important.

Lecture's over, here's an air code example of that type of
query:

SELECT T.vehicleID, T.readdate, T.mileage,
T.mileage - (SELECT TOP 1 X.mileage
FROM thetable As X
WHERE X.vehicleID = T.vehicleID
AND X.readdate < T.readdate
ORDER BY X.readdate DESC
) As Difference
FROM thetable As T
ORDER BY T.vehicleID, T.mileage
--
Marsh
MVP [MS Access]


You are correct in assuming the Date field determines the order of the
records. I apologize for the confusion. There could easily be more than
one
vehicle to track. Currently there is only one. Each vehicle is numbered
and
that number is entered into each record.

Does this give you enough to work with?


Each record of the database has a field called "Mileage" that stores
a
numeric value. What I would like to do is take the mileage from the
previous
record and subtract it from the current record for each record
entered.
How
can I do this?


Marshall Barton wrote:
Use a subquery, or, if the query must be updatable, the DMax
function.

Without knowing the table, its fields and what "previous"
means (in terms of fields in each record), I don't think I
can be more specific.


Private email from Spider:
Assume that the database has numerous records with only one field -
Mileage.
Each day the vehicle mileage is entered into a new record. What I
would
like
to be able to do is display on a form the difference between the
current
mileage & the previous days mileage for every entry.

Record 1 - Mileage = 500
Record 2 - Mileage = 750 The difference between record 2 & record 1
is
250.
This difference would be displayed on a form when the record is
selected.
Record 3 - Mileage = 100 The difference between record 2 & record 1
is
250.
This difference would be displayed on a form when the record is
selected.

What I can't figure out is how do I get the value from a previous
record
so
I can use it in the current record? I would like the mileage
difference
to
be calculated automatically each time the user enters the Mileage into
the
new days (current) record.


"Marshall Barton" wrote
Please keep the correspondence in the newsgroups. The delay
in this response is because I don't check that account all
that often and is another good reason to stick to the
newsgroups.

You still haven't defined (in terms of fields in the table)
what "previous" means. Relational databases have no
inherent order to the records, including the order they were
entered. You may have some kind of special situation such
as the mileage number always increases, but your example
does not demonstrate it, probably because of a typo, but I
can't tell for sure. The most common way of determining
"previous" is by having a date field so the order of the
records can be easily determined.

You also failed to mention whether there is a single vehicle
or multiple vehicles. This information is critical to
working out the details of a query to get the value you
want.

Without those details, all I can do is provide an example
based on the extraordinarily simple assumptions you posted:

SELECT T.mileage,
T.mileage - (SELECT Max(X.mileage)
FROM thetable As X
WHERE X.mileage < T.mileage
) As Difference
FROM thetable As T
ORDER BY T.mileage
Click to expand...
Click to expand...
Click to expand...
 
You need to make sure the combo box's properties agree with
the RowSource table/query.

The ColumnCount must match the number of fields returned by
the table/query.

The ColumnWidths property is important not only to showing
data when the box drops down (columns with a 0 width are not
visible), but also the column with the first non-zero width
is the value displayed when the list is not dropped down.

The BoundColumn property specifies which column's value is
assigned to the combo box's Value property and, if the combo
box's ControlSource is a field in the form's RecordSource,
to the field in the table.

Without knowing a lot more about what your combo boxes are
supposed to do, I can't answer your question directly, but
maybe you can use the above info to figure it out.
 
I got the values I wanted to show in the combo box list; I had to add a
table value (i.e. tblDrivers) to the RowSource property of the Combo box.



Everything looks like it should work, but when I add data to the form and
try to go to the next record, I get the following error:



The Microsoft Jet database engine cannot find a record in the table
'tblVendors' with key matching field(s) 'tblDriversLog.VendorID'.



I have looked through relationships, field types, etc. but I can't figure
out why I keep getting this error. If I eliminate the tblDrivers table, I
get the error with a different table. It doesn't matter if I use a Query
with the form, or Tables (I used the wizard to create both forms). All
tables are joined 1 to Many with Referential Integrity enforced.

Can you give me any idea what may be causing this problem?


Marshall Barton said:
You need to make sure the combo box's properties agree with
the RowSource table/query.

The ColumnCount must match the number of fields returned by
the table/query.

The ColumnWidths property is important not only to showing
data when the box drops down (columns with a 0 width are not
visible), but also the column with the first non-zero width
is the value displayed when the list is not dropped down.

The BoundColumn property specifies which column's value is
assigned to the combo box's Value property and, if the combo
box's ControlSource is a field in the form's RecordSource,
to the field in the table.

Without knowing a lot more about what your combo boxes are
supposed to do, I can't answer your question directly, but
maybe you can use the above info to figure it out.
--
Marsh
MVP [MS Access]



Spider said:
You mention using a combo box with a query. I have several on the form,
but
I do not get the results I expected. I have set the record source to a
query
that includes all the tables in the database. The control source is set to
a
field found in the query. The row source type is Table/Query. The bound
column is 1 (this doesn't seem to make any difference what value I use).
Two
of the combo boxes display nothing, and one displays the Auto Number
values.
Can you tell me what I'm doing wrong?

"Marshall Barton" wrote
Click to expand...
Click to expand...
 
How do I get rid of the '@ adelphia.net>' that's tacked onto the sender
name?

--
Spider

Marshall Barton said:
You need to make sure the combo box's properties agree with
the RowSource table/query.

The ColumnCount must match the number of fields returned by
the table/query.

The ColumnWidths property is important not only to showing
data when the box drops down (columns with a 0 width are not
visible), but also the column with the first non-zero width
is the value displayed when the list is not dropped down.

The BoundColumn property specifies which column's value is
assigned to the combo box's Value property and, if the combo
box's ControlSource is a field in the form's RecordSource,
to the field in the table.

Without knowing a lot more about what your combo boxes are
supposed to do, I can't answer your question directly, but
maybe you can use the above info to figure it out.
--
Marsh
MVP [MS Access]



Spider said:
You mention using a combo box with a query. I have several on the form,
but
I do not get the results I expected. I have set the record source to a
query
that includes all the tables in the database. The control source is set to
a
field found in the query. The row source type is Table/Query. The bound
column is 1 (this doesn't seem to make any difference what value I use).
Two
of the combo boxes display nothing, and one displays the Auto Number
values.
Can you tell me what I'm doing wrong?

"Marshall Barton" wrote
Click to expand...
Click to expand...
 
I have no specific idea. All I can say is that there
appears to be a lot of confusion and/or miscommunication
going on here. Going through your previous messages, I
still have no idea what tables, fields, keys and
relationships are involved in any of this.

At one point, you said the combo box's Record Source
includes all the tables. I can make no sense of this
because a combo box does not have a record source property.
perhaps you meant the combo box's Row Source??? But maybe
you meant the form's record source???

If you did mean the form's record source, then it still
doesn't make sense. Generally, including several tables in
a form's record source is used only to display a few
associated values in disabled and/or locked text box
controls. If you try to edit any control that is bound to a
field in any table other than the form's base table, you
will be seeing problems of the type you mentioned. If the
combo box is bound to an out of the way field in the form's
record source, then this may be the cause of the error. You
have never mentioned what purpose the combo box is supposed
to serve (for all I know at this point, the combo box should
not even be bound).
 
In the following code, I don't understand what these are:
X.mileage, thetable, X, X.readdate, T.

The others I can substitute values for from my dbase. Could you explain
these please?

SELECT T.vehicleID, T.readdate, T.mileage,
T.mileage - (SELECT TOP 1 X.mileage
FROM thetable As X
WHERE X.vehicleID = T.vehicleID
AND X.readdate < T.readdate
ORDER BY X.readdate DESC
) As Difference
FROM thetable As T
ORDER BY T.vehicleID, T.mileage

--
Spider
Marshall Barton said:
Yes, that's the core of the requirements. I'm sorry if I
seem to be nagging on you, but, for future reference, it
really helps us answer questions if we have a real rather
than oversimplified abstraction of the problem. Another
important bit of information is a list of the pertinate
table fields and their data type. If there are multiple
tables involved, the relationship primary and foreign key
fields are also important.

Lecture's over, here's an air code example of that type of
query:

SELECT T.vehicleID, T.readdate, T.mileage,
T.mileage - (SELECT TOP 1 X.mileage
FROM thetable As X
WHERE X.vehicleID = T.vehicleID
AND X.readdate < T.readdate
ORDER BY X.readdate DESC
) As Difference
FROM thetable As T
ORDER BY T.vehicleID, T.mileage
--
Marsh
MVP [MS Access]


Spider said:
You are correct in assuming the Date field determines the order of the
records. I apologize for the confusion. There could easily be more than
one
vehicle to track. Currently there is only one. Each vehicle is numbered
and
that number is entered into each record.

Does this give you enough to work with?
Click to expand...
Click to expand...
 
Spider said:
In the following code, I don't understand what these are:
X.mileage, thetable, X, X.readdate, T.

The others I can substitute values for from my dbase. Could you explain
these please?

SELECT T.vehicleID, T.readdate, T.mileage,
T.mileage - (SELECT TOP 1 X.mileage
FROM thetable As X
WHERE X.vehicleID = T.vehicleID
AND X.readdate < T.readdate
ORDER BY X.readdate DESC
) As Difference
FROM thetable As T
ORDER BY T.vehicleID, T.mileage
Click to expand...


The T and X are aliases for your table name. This is
necessary to distinguish which instance of a field you are
referring to.

Since you never told me the name of your table, I just used
"thetable" to indicate where you should put your oen table
name.

"mileage" and "readdate" are the names I used for the fields
in your table. replace them with whatever names you
actually have in the table.

Note, for future reference, that all this confusion could
have been avoided if you had used your real table and field
names when asking your original question.
 
Back
Top