Mileage

[mcell0042]

I'm trying to track mileage between my location to various other places in the USA. So what i did was create a table and inputed all of the cities, states, and what the mileage was from my location. There are maybe 700 different cities that we deal with. so I thought that maybe if I put it all in a table that I can link it to my other and can spit out the mileage. The reason why I need the mileage is because we have different rates based on mileage. I chose to use the id field as my primary key because there are more than one city. So what I did was create a field in my other table called id and i'm trying to link it to my new table but when i do its only showing me info from one table or the other. What I'm trying to do is update my id number in my older table where the City and State = the City and State in my new table.

 

[LittleSmudge]

Using "id" is going to lead to bucket fulls of confusion in the long term.

Set the table name to somethink like
tblMilesFromHome and the Id to MilesFromHomeId

so
tblMilesFromHome
MilesFromhomeId
City Type=Text
State Type=Text
Distance Type=Integer



Then add a MilesFromHomeRef in your 'Old table'
not replace Id with Ref in foreign Key fields so that you know it is a Foreign key.

Then you need a query like

UPDATE tblOld INNER JOIN tblMilesFromHome
ON tblOld.City = tblMilesFromHome.City AND tblOldState = tblMilesFromHome.State SET MilesFromHomeRef = MilesFromHomeId




'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.