Pouplate a Field based on Data From Another Field

[tj007]

Hello, I am working on a major project for which I need assistance. I have an Access 2000 DB with three fields of importance

Stop
Loc_Id
Cumulative Distance

"Stop" This number field hold a 5 digit number e.g 01103 which is a bus route and stop number. The first two digits are the Route Number, and the last three digits are the number of stops within the Route (sequentially) that is made by the bus.

"Loc_ID" This is a autonumber field based on the "Stop" field. It must be unique.

"Cumulative Distance" This is a number field that hold the distance between each stop within the Route. The last row in the "Stop" field show the total distance of the route as each Stop is a cumalative distance of the total distance of the Route.
For example:
Stop Cum_Dist
01002 0.26
01003 0.38
01004 1.33
01005 1.58
01133 28.78 Miles

1. I would like to add a field to the database name "Route" that reflect the first two digits of the "Stop" field. For example, if a field begin with 01, populate the "Route" field with 1 and if the field begin with 02 populate the "Route" field with 2


2. If a stop is added, moved, or removed. A recalculation of distance from the previous and to the next stop need to be recalculated. Stop numbers for (every route that the stop served) must be recalculated to reflect the new order of the bus stops on that route only.

Whew, can this be done?

 

[JeremyNYC]

TJ,

Sort of. You're definitely heading in the right direction by splitting out the route number from the stop number. You should end up with one field that holds just the route number and one field that holds just the stop number. These should be numeric fields.

To get there, create two new fields in your table, (I'll go with Rt and Stp in my sample). Then, make an update query to put data in those new fields (I'll show you the sql in a second). After you run that, and check that it gave you the results you expected, go ahead and delete the original field named Stop.

Here's the sql:
UPDATE YourTableName SET [rt] = left([stop],2), [stp]= right([stop],3)

That's all looking good.

But, the bit about storing the total distance is probably a mistake. In general, and definitely in this case, database theory tells us not to store calculated values, as they can lead to conflicts. Instead, you should build queries that reveal the calculated value you need. In your case, I would suggest having a form that shows info about the bus route, with a subform that shows the info for each stop. The total distance would be displayed on the main form, and would be calculdated with a simple dsum:
dsum("Cum_Dist", "YourTableName", "Rt = " & me!txtRt)
where txtRt is a text box on the main form that displays the route number.

Also, this distance field really isn't the cummulative distance, it's the incremental distance, so you might want to think about renaming that field.

I know this is a lot, and I've left out some details, but if you take a stab at this, and keep pushing on it a bit, you ought to be able to get fairly close. If it's not working, post back with what you've done, what your results were, and what you were expecting, and we'll be able to get you the rest of the distance.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[tj007]

Thanks Jeremy I will get back with the results.

 

[tj007]

Jeremy, the query works great. I was able to separate the Route & Stops perfectly. I have not deleted the other field "Stop". I have created a main form with the route information and a sub form with the stop info. I have not been able to get the dsum to function. I created a txt box and added the line

dsum("Cum_Dist", "BSALL", "Rt = " & me!txtRt)

I get Compile error Expected:=

 

[tj007]

Jeremy, I over looked a field in the database name DIST_STOP which is the actual distance between each stop. The CUM_DIST field is populated from the distance increments from the DIST_STOP field. Again, my question is how do I auto calculate each DIST_STOP to reflect CUM_DIST for each stop?

 

[JeremyNYC]

TJ,

First, I missed something. I assume the new fields you created are text fields. If that's the case, you'll need to add single quotes to the dsum:
dsum("Cum_Dist", "BSALL", "Rt = '" & me!txtRt & "'")

That should work.

I don't think you should store a cummulative distance in your table. For more on why, see the Database Design link in the Developers' section of my website.

I have not worked with queries that calculate based on previous records in the query, though I know they're doable. My first thought would be to google around to try to find out how others have done it, or post something here about building a query with a running sum.

But you could also just build a function that returns the cummulative distance for each stop, and include that in your query.

I created a table, function, and query without checking the names you were using, which may make a little work for you in renaming, but assuming the route and stop fields are stings, create...

Create the following funciton:
Function fSngCumDist(strRoute As String, strStop As String) As Single
fSngCumDist = DSum("distance", "tblRoute", "route = '" & strRoute & "' AND stop <= '" & strStop & "'")
End Function

and then create the following query:
SELECT tblRoute.Route, tblRoute.Stop, tblRoute.Distance, fsngcumdist([route],[stop]) AS Cum
FROM tblRoute
ORDER BY tblRoute.Route, tblRoute.Stop;

Should be easy enough to adjust if the fields are numeric (which they should be).

I hope that's of some value.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[tj007]

Again Thanks

The new fields RT & Stp are number fields. I tried the new code, but I have the same error.

 

[PHV]

dsum("Cum_Dist", "BSALL", "Rt = " & me!txtRt)
I get Compile error Expected:=
DSum is a function returning a value and there is no variable to receive it, i.e. DSum must be at the right of an equal sign in an expression.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884