Calculating a Cumulative value

M

MValentine

I posted this in the "General" section of the Access help form and realized I
probably should have posted it under "Queries".

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
 
D

Dale Fye

The following should work:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) as Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ])
AND ((PED.TERMINAL_1) Between [ ENTER START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Another way (this is untested!!! You probably need to try both to determine
which is quicker):

SELECT P1.ROUTE_1, P1.TERMINAL_1, P1.BACKSPAN_1,
SUM(P2.BackSpan_1) as Cum_Dist
FROM PED P1 INNER JOIN P2
ON P1.Route_1 = P2.Route_1
WHERE P1.ROUTE_1=[ Enter Route ]
AND P1.TERMINAL_1 Between [ ENTER START ] And [ENTER END ]
AND P2.TERMINAL_1 Between [ ENTER START ] AND P1.TERMINAL_1
GROUP BY P1.ROUTE_1, P1.TERMINAL, P1.BACKSPAN_1
ORDER BY P1.TERMINAL_1;

HTH
Dale
 
M

MValentine

Dale
THANKS. The first solution which you provided did just what I needed. Worked
great. Now I have come across another issue which you may be able to help me
with. My query is running against an external dBASEIII database. My
TERMINAL_1 listing is not appearing in sequence based on numeric value. For
example I am receiving the following:
1
10
11
12
2
3
4......
Can I correct this so that the values are listed based on numeric sequence?

THANKS AGAIN for the assistance.
Michael


Dale Fye said:
The following should work:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) as Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ])
AND ((PED.TERMINAL_1) Between [ ENTER START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Another way (this is untested!!! You probably need to try both to determine
which is quicker):

SELECT P1.ROUTE_1, P1.TERMINAL_1, P1.BACKSPAN_1,
SUM(P2.BackSpan_1) as Cum_Dist
FROM PED P1 INNER JOIN P2
ON P1.Route_1 = P2.Route_1
WHERE P1.ROUTE_1=[ Enter Route ]
AND P1.TERMINAL_1 Between [ ENTER START ] And [ENTER END ]
AND P2.TERMINAL_1 Between [ ENTER START ] AND P1.TERMINAL_1
GROUP BY P1.ROUTE_1, P1.TERMINAL, P1.BACKSPAN_1
ORDER BY P1.TERMINAL_1;

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


MValentine said:
I posted this in the "General" section of the Access help form and realized I
probably should have posted it under "Queries".

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...
Click to expand...
 
D

Dale Fye

Yes. The field is obviously a text field in the dbase database, which also
means that the BETWEEN clause in the query needs to be reworked. You need to
use the VAL( ) function to convert this field to a numeric value. Try it like:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND Val(P.Terminal_1) <= (val(PED.Terminal_1)) as Cum_Dist
FROM PED
WHERE PED.ROUTE_1=[ Enter Route ])
AND VAL(PED.TERMINAL_1) Between VAL([ ENTER START ]) And VAL([ENTER END ])))
ORDER BY VAL(PED.TERMINAL_1);

You could probably get rid of the VAL( ) function wrapping the [Enter Start]
and [Enter End] parameters if you defined the parameters as numeric (Long or
integer) in the query.

Dale

--
Email address is not valid.
Please reply to newsgroup only.


MValentine said:
Dale
THANKS. The first solution which you provided did just what I needed. Worked
great. Now I have come across another issue which you may be able to help me
with. My query is running against an external dBASEIII database. My
TERMINAL_1 listing is not appearing in sequence based on numeric value. For
example I am receiving the following:
1
10
11
12
2
3
4......
Can I correct this so that the values are listed based on numeric sequence?

THANKS AGAIN for the assistance.
Michael


Dale Fye said:
The following should work:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) as Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ])
AND ((PED.TERMINAL_1) Between [ ENTER START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Another way (this is untested!!! You probably need to try both to determine
which is quicker):

SELECT P1.ROUTE_1, P1.TERMINAL_1, P1.BACKSPAN_1,
SUM(P2.BackSpan_1) as Cum_Dist
FROM PED P1 INNER JOIN P2
ON P1.Route_1 = P2.Route_1
WHERE P1.ROUTE_1=[ Enter Route ]
AND P1.TERMINAL_1 Between [ ENTER START ] And [ENTER END ]
AND P2.TERMINAL_1 Between [ ENTER START ] AND P1.TERMINAL_1
GROUP BY P1.ROUTE_1, P1.TERMINAL, P1.BACKSPAN_1
ORDER BY P1.TERMINAL_1;

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


MValentine said:
I posted this in the "General" section of the Access help form and realized I
probably should have posted it under "Queries".

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...
Click to expand...
Click to expand...
 
M

MValentine

Dale
Thanks for the quick responce. I tried what you provided. However I am
getting the following syntex error:
Syntex error. in query expression '(SELECT SUM(backspan_1) FROM PED P
WHERE P.Route_1=PED.Route_1
AND Val(P.Terminal_1) <=(val(PED.Terminal_1)) as Cum_Dist
FROM PED
WHERE PED.ROUTE_1=[ Enter Route])
AND VAL(PED.TERMINAL_1) Between VAL([ ENTER START ]) An'.

I opened the dBASEIII linked database in Access and the field is shown as a
number field.

Your assistance is greatly appreciated.

Michael


Dale Fye said:
Yes. The field is obviously a text field in the dbase database, which also
means that the BETWEEN clause in the query needs to be reworked. You need to
use the VAL( ) function to convert this field to a numeric value. Try it like:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND Val(P.Terminal_1) <= (val(PED.Terminal_1)) as Cum_Dist
FROM PED
WHERE PED.ROUTE_1=[ Enter Route ])
AND VAL(PED.TERMINAL_1) Between VAL([ ENTER START ]) And VAL([ENTER END ])))
ORDER BY VAL(PED.TERMINAL_1);

You could probably get rid of the VAL( ) function wrapping the [Enter Start]
and [Enter End] parameters if you defined the parameters as numeric (Long or
integer) in the query.

Dale

--
Email address is not valid.
Please reply to newsgroup only.


MValentine said:
Dale
THANKS. The first solution which you provided did just what I needed. Worked
great. Now I have come across another issue which you may be able to help me
with. My query is running against an external dBASEIII database. My
TERMINAL_1 listing is not appearing in sequence based on numeric value. For
example I am receiving the following:
1
10
11
12
2
3
4......
Can I correct this so that the values are listed based on numeric sequence?

THANKS AGAIN for the assistance.
Michael


Dale Fye said:
The following should work:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) as Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ])
AND ((PED.TERMINAL_1) Between [ ENTER START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Another way (this is untested!!! You probably need to try both to determine
which is quicker):

SELECT P1.ROUTE_1, P1.TERMINAL_1, P1.BACKSPAN_1,
SUM(P2.BackSpan_1) as Cum_Dist
FROM PED P1 INNER JOIN P2
ON P1.Route_1 = P2.Route_1
WHERE P1.ROUTE_1=[ Enter Route ]
AND P1.TERMINAL_1 Between [ ENTER START ] And [ENTER END ]
AND P2.TERMINAL_1 Between [ ENTER START ] AND P1.TERMINAL_1
GROUP BY P1.ROUTE_1, P1.TERMINAL, P1.BACKSPAN_1
ORDER BY P1.TERMINAL_1;

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

I posted this in the "General" section of the Access help form and realized I
probably should have posted it under "Queries".

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...
Click to expand...
Click to expand...
 
M

MValentine

I need to make a correction from my last email. The TERMINAL_1 field is a
text field. I was looking at the BACKSPAN_1 field when I informed you that it
was a number field.

SORRY

Dale Fye said:
Yes. The field is obviously a text field in the dbase database, which also
means that the BETWEEN clause in the query needs to be reworked. You need to
use the VAL( ) function to convert this field to a numeric value. Try it like:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND Val(P.Terminal_1) <= (val(PED.Terminal_1)) as Cum_Dist
FROM PED
WHERE PED.ROUTE_1=[ Enter Route ])
AND VAL(PED.TERMINAL_1) Between VAL([ ENTER START ]) And VAL([ENTER END ])))
ORDER BY VAL(PED.TERMINAL_1);

You could probably get rid of the VAL( ) function wrapping the [Enter Start]
and [Enter End] parameters if you defined the parameters as numeric (Long or
integer) in the query.

Dale

--
Email address is not valid.
Please reply to newsgroup only.


MValentine said:
Dale
THANKS. The first solution which you provided did just what I needed. Worked
great. Now I have come across another issue which you may be able to help me
with. My query is running against an external dBASEIII database. My
TERMINAL_1 listing is not appearing in sequence based on numeric value. For
example I am receiving the following:
1
10
11
12
2
3
4......
Can I correct this so that the values are listed based on numeric sequence?

THANKS AGAIN for the assistance.
Michael


Dale Fye said:
The following should work:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) as Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ])
AND ((PED.TERMINAL_1) Between [ ENTER START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Another way (this is untested!!! You probably need to try both to determine
which is quicker):

SELECT P1.ROUTE_1, P1.TERMINAL_1, P1.BACKSPAN_1,
SUM(P2.BackSpan_1) as Cum_Dist
FROM PED P1 INNER JOIN P2
ON P1.Route_1 = P2.Route_1
WHERE P1.ROUTE_1=[ Enter Route ]
AND P1.TERMINAL_1 Between [ ENTER START ] And [ENTER END ]
AND P2.TERMINAL_1 Between [ ENTER START ] AND P1.TERMINAL_1
GROUP BY P1.ROUTE_1, P1.TERMINAL, P1.BACKSPAN_1
ORDER BY P1.TERMINAL_1;

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

I posted this in the "General" section of the Access help form and realized I
probably should have posted it under "Queries".

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...
Click to expand...
Click to expand...
 
D

Dale Fye

Sorry, I missed a left parenthesis. Should be:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND Val(P.Terminal_1) <= val(PED.Terminal_1)) as Cum_Dist
FROM PED
WHERE PED.ROUTE_1=[ Enter Route ])
AND VAL(PED.TERMINAL_1) Between VAL([ ENTER START ]) And VAL([ENTER END ])))
ORDER BY VAL(PED.TERMINAL_1);


MValentine said:
I need to make a correction from my last email. The TERMINAL_1 field is a
text field. I was looking at the BACKSPAN_1 field when I informed you that
it
was a number field.

SORRY

Dale Fye said:
Yes. The field is obviously a text field in the dbase database, which
also
means that the BETWEEN clause in the query needs to be reworked. You
need to
use the VAL( ) function to convert this field to a numeric value. Try it
like:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND Val(P.Terminal_1) <= (val(PED.Terminal_1)) as Cum_Dist
FROM PED
WHERE PED.ROUTE_1=[ Enter Route ])
AND VAL(PED.TERMINAL_1) Between VAL([ ENTER START ]) And VAL([ENTER
END ])))
ORDER BY VAL(PED.TERMINAL_1);

You could probably get rid of the VAL( ) function wrapping the [Enter
Start]
and [Enter End] parameters if you defined the parameters as numeric (Long
or
integer) in the query.

Dale

--
Email address is not valid.
Please reply to newsgroup only.


MValentine said:
Dale
THANKS. The first solution which you provided did just what I needed.
Worked
great. Now I have come across another issue which you may be able to
help me
with. My query is running against an external dBASEIII database. My
TERMINAL_1 listing is not appearing in sequence based on numeric value.
For
example I am receiving the following:
1
10
11
12
2
3
4......
Can I correct this so that the values are listed based on numeric
sequence?

THANKS AGAIN for the assistance.
Michael


:

The following should work:

SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.BACKSPAN_1,
(SELECT SUM(Backspan_1) FROM PED P
WHERE P.Route_1 = PED.Route_1
AND P.Terminal_1 <= PED.Terminal_1) as Cum_Dist
FROM PED
WHERE (((PED.ROUTE_1)=[ Enter Route ])
AND ((PED.TERMINAL_1) Between [ ENTER START ] And [ENTER END ]))
ORDER BY PED.TERMINAL_1;

Another way (this is untested!!! You probably need to try both to
determine
which is quicker):

SELECT P1.ROUTE_1, P1.TERMINAL_1, P1.BACKSPAN_1,
SUM(P2.BackSpan_1) as Cum_Dist
FROM PED P1 INNER JOIN P2
ON P1.Route_1 = P2.Route_1
WHERE P1.ROUTE_1=[ Enter Route ]
AND P1.TERMINAL_1 Between [ ENTER START ] And [ENTER END ]
AND P2.TERMINAL_1 Between [ ENTER START ] AND P1.TERMINAL_1
GROUP BY P1.ROUTE_1, P1.TERMINAL, P1.BACKSPAN_1
ORDER BY P1.TERMINAL_1;

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

I posted this in the "General" section of the Access help form and
realized I
probably should have posted it under "Queries".

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...
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 4

Top