Expression

[CharlesCount via AccessMonster.com]

I really don't know how to pose this question so I will give an example in
Excel: Cell 2B is eaqual to Cell 2A - Cell1A I would like use a similar
calculation in Access for all records in a field or calulate the different
between the record in the same line and the previous record.

I hope I gave edquate information.

Charles

 

[NetworkTrade]

for this in a form/report - create a new unbound field and for this field
define its source as
=[Cell 2A] - [Cell1A]

where of course [Cell.....] refers to the actual name of those other
fields/controls on your form/report that already have values.

in a query it is a slight different method.....first you create the query in
design view adding all the fields....then you create a calculated field by
using a blank column and putting in your formula ; in this case you would
put:

Cell2B: [Cell 2A] - [Cell1A]
the text to the left of the : becomes the column heading and the text to the
right of the : is of course the calculation involving other columns found in
this query

 

[CharlesCount via AccessMonster.com]

These example appear to use 2 diiferent control field, in my case I need to
use the same field but find the difference between the raw.
Excel Fromula
CellA1 = 2
CellA2 = 1 Cell B2 = 3
CellA3 = 5 Cell B3 = 8 Etc


Access
Trip Location Adometer reading Miles Traveled
10 Main Street 12588
12 West Ave 12598 10
10 North Blvd 12613 15

I might have as many as 1000 trips daily

for this in a form/report - create a new unbound field and for this field
define its source as
=[Cell 2A] - [Cell1A]

where of course [Cell.....] refers to the actual name of those other
fields/controls on your form/report that already have values.

in a query it is a slight different method.....first you create the query in
design view adding all the fields....then you create a calculated field by
using a blank column and putting in your formula ; in this case you would
put:

Cell2B: [Cell 2A] - [Cell1A]
the text to the left of the : becomes the column heading and the text to the
right of the : is of course the calculation involving other columns found in
this query

I really don't know how to pose this question so I will give an example in
Excel: Cell 2B is eaqual to Cell 2A - Cell1A I would like use a similar

[quoted text clipped - 4 lines]

Charles

 

[NetworkTrade]

well....kind of depends on your user reality and your's too...

yes, excel is more friendly to the type of add you display because you point
it directly to the cell of a different row/record; which is not an aspect of
a database...so doing the math in excel and then importing it is one option
out there...keep it as a possible approach....

another idea; enter a startOdometer and an endOdometer....makes the math
calc easy ; if you control the user entry screen this is really a good way to
go; because it makes it simple....though I know the user reality maybe that
they only record the ending odometer

because you right now rely on the prior record's Odometer as the starting
point of the calc...and the fact that database tables can be sorted/resorted
then you will need a field that can clearly and always be findable as the
immediately preceeding record...i.e. current record ID-1....at which point an
sql statement is feasible assuming you are in a query...

am giving general; and not particularly immediately helpful advice - sorry
about that...
--
NTC

These example appear to use 2 diiferent control field, in my case I need to
use the same field but find the difference between the raw.
Excel Fromula
CellA1 = 2
CellA2 = 1 Cell B2 = 3
CellA3 = 5 Cell B3 = 8 Etc


Access
Trip Location Adometer reading Miles Traveled
10 Main Street 12588
12 West Ave 12598 10
10 North Blvd 12613 15

I might have as many as 1000 trips daily

for this in a form/report - create a new unbound field and for this field
define its source as
=[Cell 2A] - [Cell1A]

where of course [Cell.....] refers to the actual name of those other
fields/controls on your form/report that already have values.

in a query it is a slight different method.....first you create the query in
design view adding all the fields....then you create a calculated field by
using a blank column and putting in your formula ; in this case you would
put:

Cell2B: [Cell 2A] - [Cell1A]
the text to the left of the : becomes the column heading and the text to the
right of the : is of course the calculation involving other columns found in
this query

I really don't know how to pose this question so I will give an example in
Excel: Cell 2B is eaqual to Cell 2A - Cell1A I would like use a similar

[quoted text clipped - 4 lines]

Charles

 

[John Spencer]

Do you have something else that determines the vehicle and perhaps the date
or something that tells you how the records are ordered and therefore which
is the first record in any group?

For example in your sample data, what determis that the trip started at 10
Main Street and proceeded to 12 West Ave and then went on to 10 North Blvd?
What data in the records do you have that say 10 Main Street was the first
location? What data in the records do you have that says the three records
are related to each other?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

These example appear to use 2 diiferent control field, in my case I need
to
use the same field but find the difference between the raw.
Excel Fromula
CellA1 = 2
CellA2 = 1 Cell B2 = 3
CellA3 = 5 Cell B3 = 8 Etc


Access
Trip Location Adometer reading Miles Traveled
10 Main Street 12588
12 West Ave 12598 10
10 North Blvd 12613 15

I might have as many as 1000 trips daily

for this in a form/report - create a new unbound field and for this field
define its source as
=[Cell 2A] - [Cell1A]

where of course [Cell.....] refers to the actual name of those other
fields/controls on your form/report that already have values.

in a query it is a slight different method.....first you create the query
in
design view adding all the fields....then you create a calculated field by
using a blank column and putting in your formula ; in this case you would
put:

Cell2B: [Cell 2A] - [Cell1A]
the text to the left of the : becomes the column heading and the text to
the
right of the : is of course the calculation involving other columns found
in
this query

I really don't know how to pose this question so I will give an example
in
Excel: Cell 2B is eaqual to Cell 2A - Cell1A I would like use a similar

[quoted text clipped - 4 lines]

Charles

 

[CharlesCount via AccessMonster.com]

I have many fields that are tied into these repords, TDate, Shift Number and
Vehicle Number are 3 thre of them.

Charles

Do you have something else that determines the vehicle and perhaps the date
or something that tells you how the records are ordered and therefore which
is the first record in any group?

For example in your sample data, what determis that the trip started at 10
Main Street and proceeded to 12 West Ave and then went on to 10 North Blvd?
What data in the records do you have that say 10 Main Street was the first
location? What data in the records do you have that says the three records
are related to each other?

These example appear to use 2 diiferent control field, in my case I need
to

[quoted text clipped - 37 lines]

 

[John Spencer]

What is recorded in TDate. Is that the start or end date and TIME of each
segment of a trip?

Again if you are putting the destinations in a specific order, how (what
fields) do you do that? It would be helpful if you also told us about the
data types. Also the primary key field(s) in your table would be nice to
know.

The following correlated subquery may be sufficient to give you the results
you are looking for:
Assumptions:
TDate is just a date
Trips all take place on one date
Trips all take place during one shift


SELECT Location, Reading, Miles,
Odometer, Odometer -
(SELECT Min(Odometer)
FROM YourTable as Y1
WHERE Y1.VehicleNumber = YourTable.VehicleNumber
and Y1.TDate = YourTable.TDate
and Y1.ShiftNumber = YourTable.ShiftNumber) as MilesTraveled
FROM YourTable

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have many fields that are tied into these repords, TDate, Shift Number
and
Vehicle Number are 3 thre of them.

Charles

Do you have something else that determines the vehicle and perhaps the
date
or something that tells you how the records are ordered and therefore
which
is the first record in any group?

For example in your sample data, what determis that the trip started at 10
Main Street and proceeded to 12 West Ave and then went on to 10 North
Blvd?
What data in the records do you have that say 10 Main Street was the first
location? What data in the records do you have that says the three
records
are related to each other?

These example appear to use 2 diiferent control field, in my case I need
to

[quoted text clipped - 37 lines]

Charles

 

[John Spencer]

SELECT Vehicle
, Location
, Odometer
, Odometer -
(SELECT Min(Odometer)
FROM YourTable as Y1
WHERE Y1.VehicleNumber = YourTable.VehicleNumber
and Y1.TDate = YourTable.TDate
and Y1.ShiftNumber = YourTable.ShiftNumber) as MilesTraveled
FROM YourTable

Sorry about that I was inventing new fields

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

What is recorded in TDate. Is that the start or end date and TIME of each
segment of a trip?

Again if you are putting the destinations in a specific order, how (what
fields) do you do that? It would be helpful if you also told us about
the data types. Also the primary key field(s) in your table would be nice
to know.

The following correlated subquery may be sufficient to give you the
results you are looking for:
Assumptions:
TDate is just a date
Trips all take place on one date
Trips all take place during one shift


SELECT Location, Reading, Miles,
Odometer, Odometer -
(SELECT Min(Odometer)
FROM YourTable as Y1
WHERE Y1.VehicleNumber = YourTable.VehicleNumber
and Y1.TDate = YourTable.TDate
and Y1.ShiftNumber = YourTable.ShiftNumber) as MilesTraveled
FROM YourTable

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I have many fields that are tied into these repords, TDate, Shift Number
and
Vehicle Number are 3 thre of them.

Charles

Do you have something else that determines the vehicle and perhaps the
date
or something that tells you how the records are ordered and therefore
which
is the first record in any group?

For example in your sample data, what determis that the trip started at
10
Main Street and proceeded to 12 West Ave and then went on to 10 North
Blvd?
What data in the records do you have that say 10 Main Street was the
first
location? What data in the records do you have that says the three
records
are related to each other?

These example appear to use 2 diiferent control field, in my case I
need
to
[quoted text clipped - 37 lines]

Charles