Linking data in records

[Ian]

Is it possible to "link" 2 records in a table, such that a calculation can
use information from 2 "consecutive" records?

Let me explain. We are working on a database system where engineers will
prepare service reports, timesheets, mileage sheets and expense claims
within the database.

On the mileage sheet, it would be very helpful if we could enter only start
mileage and business mileage for a journey. When the next journey is
started, the start mileage could be accessed by the previous record and used
to calculate any private miles (eg Journey2 start - journey1 start -
business miles = private miles). This would then be entered in another field
in the table.

It appears to me that the consecutive records would need to be identified by
both date/time and engineers name.

Any ideas?

Ian

 

[Jeff Boyce]

Ian

Storing "calculated values" in a table is rarely necessary, nor a good idea.
Use a query to derive the calculated value when needed.

On the other hand, if you wish to track both business and private mileage,
why not just have the users enter that info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ian]

Hi Jeff

Storing "calculated values" in a table is rarely necessary, nor a good
idea.

I thought that might be the response, thought the values wouldn't necessarly
have to be stored, just the formula.

Use a query to derive the calculated value when needed.

Being relatively new to Access, I've never set up a query, but I'll pass the
suggestion on to a colleague who may understand what this entails and
whether it's possible/practical.

On the other hand, if you wish to track both business and private mileage,
why not just have the users enter that info?

It's all about minimising possible errors. If they only need to enter 2
values, there are only 2 chances of getting it wrong ;-)

That might be the way to go, though. It's certainly the simplest in terms of
application.

Ian

 

[Guest]

Why not just include both fields in the same record?

 

[Ian]

That may be the way to go, but I'm trying to minimise the amount of data
input.

This is the situation:
An engineer leaves home to do a job and records the start mileage. On return
home, he can enter the business miles, end mileage or both.
He goes out in the evening, using the company car.
The following morning, he has a new start mileage, but this doesn't match
the end mileage of the previous day, the difference being private miles.
Although on the input form there is a calculation to work out the "total"
personal miles, this is not a daily basis, so it's not useful for keeping a
check on the data as it's being entered.
The ideal would be for the private miles to be updated in one record as soon
as there is a start mileage entered in the next record. The difficulty is
determining exactly which is the "last" and "next" record as the engineer
may not necessarily enter the data in order.

It's easy to do in Excel as we've been using this for the last few years
with no problems. The issue now is that we are trying to incorporate the
data into the database for ease of data management. We may have to accept
that we will need to work in a very different way.

We can extract the data we need with just the start and business mileage
entered. The problem comes when a number is incorrectly entered. The effects
may not be noticed until much later on, so a daily check would be the
preferred option.

Ian