Calculating a Cumulative value

M

MValentine

I have a field called "backspan" which is a numerical value. I am wanting to
create a field called "cumulative distance". I want cumulative distance to be
based on a progressive sum of the "backspan". I have created a query which is
based on a Route and prompts the operator to enter a start and stop point,
"terminal", along the route. Below is a sample of a query result. I want to
have the query to calculate the cumulative distance.

ROUTE_1 TERMINAL_1 BACKSPAN_1 CUM_DISTANCE
17B-5L-11L-8L-7R 1 220
17B-5L-11L-8L-7R 2 190
17B-5L-11L-8L-7R 3 164
17B-5L-11L-8L-7R 4 146
17B-5L-11L-8L-7R 5 156
17B-5L-11L-8L-7R 6 160

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [ ENTER
START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Thnaks for any assistance.

Michael
 
M

MValentine

MValentine said:
I have a field called "backspan" which is a numerical value. I am wanting to
create a field called "cumulative distance". I want cumulative distance to be
based on a progressive sum of the "backspan". I have created a query which is
based on a Route and prompts the operator to enter a start and stop point,
"terminal", along the route. Below is a sample of a query result. I want to
have the query to calculate the cumulative distance.

ROUTE_1 TERMINAL_1 BACKSPAN_1 CUM_DISTANCE
17B-5L-11L-8L-7R 1 220
17B-5L-11L-8L-7R 2 190
17B-5L-11L-8L-7R 3 164
17B-5L-11L-8L-7R 4 146
17B-5L-11L-8L-7R 5 156
17B-5L-11L-8L-7R 6 160

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [ ENTER
START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Thnaks for any assistance.

Michael
Click to expand...

I was doing some addition thinking and was wondering, should I be doing this
in a query or should the cumulative distance be done in a report based on the
base query?

Michael
 
W

Wayne-I-M

Hi

You can do it either in a report - create a text box and the running sum
option

or

You can use a query
RunningSum: DSum("[FieldName]","[TableName]","[ID] <= " & [ID])

Note - I have assumed that your ID field is a sequential number or
autonumber and is in your query - it's not on the sample you posted

Hope this helps

--
Wayne
Manchester, England.



MValentine said:
MValentine said:
I have a field called "backspan" which is a numerical value. I am wanting to
create a field called "cumulative distance". I want cumulative distance to be
based on a progressive sum of the "backspan". I have created a query which is
based on a Route and prompts the operator to enter a start and stop point,
"terminal", along the route. Below is a sample of a query result. I want to
have the query to calculate the cumulative distance.

ROUTE_1 TERMINAL_1 BACKSPAN_1 CUM_DISTANCE
17B-5L-11L-8L-7R 1 220
17B-5L-11L-8L-7R 2 190
17B-5L-11L-8L-7R 3 164
17B-5L-11L-8L-7R 4 146
17B-5L-11L-8L-7R 5 156
17B-5L-11L-8L-7R 6 160

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [ ENTER
START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Thnaks for any assistance.

Michael
Click to expand...

I was doing some addition thinking and was wondering, should I be doing this
in a query or should the cumulative distance be done in a report based on the
base query?

Michael
Click to expand...
 
M

MValentine

Wayne
Thank you for the responce. I do not have an "ID" field. I am linked to an
external dBASE III file.

Michael

Wayne-I-M said:
Hi

You can do it either in a report - create a text box and the running sum
option

or

You can use a query
RunningSum: DSum("[FieldName]","[TableName]","[ID] <= " & [ID])

Note - I have assumed that your ID field is a sequential number or
autonumber and is in your query - it's not on the sample you posted

Hope this helps

--
Wayne
Manchester, England.



MValentine said:
MValentine said:
I have a field called "backspan" which is a numerical value. I am wanting to
create a field called "cumulative distance". I want cumulative distance to be
based on a progressive sum of the "backspan". I have created a query which is
based on a Route and prompts the operator to enter a start and stop point,
"terminal", along the route. Below is a sample of a query result. I want to
have the query to calculate the cumulative distance.

ROUTE_1 TERMINAL_1 BACKSPAN_1 CUM_DISTANCE
17B-5L-11L-8L-7R 1 220
17B-5L-11L-8L-7R 2 190
17B-5L-11L-8L-7R 3 164
17B-5L-11L-8L-7R 4 146
17B-5L-11L-8L-7R 5 156
17B-5L-11L-8L-7R 6 160

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [ ENTER
START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Thnaks for any assistance.

Michael
Click to expand...

I was doing some addition thinking and was wondering, should I be doing this
in a query or should the cumulative distance be done in a report based on the
base query?

Michael
Click to expand...
Click to expand...
 
W

Wayne-I-M

No problems. The running sum will still work on the report.
Create a text box and use this as the source
=Sum(Nz([FieldName]))
Running sum = Yes (over group or overall depending on what you want it to do)

--
Wayne
Manchester, England.



MValentine said:
Wayne
Thank you for the responce. I do not have an "ID" field. I am linked to an
external dBASE III file.

Michael

Wayne-I-M said:
Hi

You can do it either in a report - create a text box and the running sum
option

or

You can use a query
RunningSum: DSum("[FieldName]","[TableName]","[ID] <= " & [ID])

Note - I have assumed that your ID field is a sequential number or
autonumber and is in your query - it's not on the sample you posted

Hope this helps

--
Wayne
Manchester, England.



MValentine said:
:

I have a field called "backspan" which is a numerical value. I am wanting to
create a field called "cumulative distance". I want cumulative distance to be
based on a progressive sum of the "backspan". I have created a query which is
based on a Route and prompts the operator to enter a start and stop point,
"terminal", along the route. Below is a sample of a query result. I want to
have the query to calculate the cumulative distance.

ROUTE_1 TERMINAL_1 BACKSPAN_1 CUM_DISTANCE
17B-5L-11L-8L-7R 1 220
17B-5L-11L-8L-7R 2 190
17B-5L-11L-8L-7R 3 164
17B-5L-11L-8L-7R 4 146
17B-5L-11L-8L-7R 5 156
17B-5L-11L-8L-7R 6 160

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ]) AND ((PED.TERMINAL_1) Between [ ENTER
START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Thnaks for any assistance.

Michael

I was doing some addition thinking and was wondering, should I be doing this
in a query or should the cumulative distance be done in a report based on the
base query?

Michael
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating a Cumulative value 6

Top