Spliting tables

[John K]

Hello,
I will have a table that contains the driving miles from point a to point b. This table will have many records from each state. I am using access 2010 on windows 7. Would it be best to split this table by states? At this point I dont know the total number of records I will have if I leave all the data in one table. I do know it will be a very large table.
Thank you,
John

 

[boblarson]

No, you should not split it. It all is the same data, so it all goes in the same table just with an identifier. Access tables are normally thin and TALL. So, millions of records are not a problem for it.

 

[gemma-the-husky]

one issue is whether you store directions twice

let's say you have a table

startlocation endlocation distance
LA sacramento 200 miles (I don't know - I'm in the UK)

do you also have to store

startlocation endlocation distance
sacramento LA 200 miles


as other wise you may find you need to search twice for any trip. Tricky - I am not sure the best way to do this. probably store once, with the places always in an agreed order - say alphabetic. then you only need one search for a given trip.

however you do need a union query to find all the distances for a given place - as LA will be the startlocation for LA-Sacramento, but the endlocation for Fresno-LA.


----
sorry. let's get this right, now.

if you need road distances, then you have to work them out, and use the table, as calculating road distances is non-trivial

if you want straightline distances, then the calculation is trivial - The correct procedure is to just store the geolocation of each place - lat and long, and then simply use a great earth circle calculation to work out the distance between any two points, on demand, using the lat longs

(it's similar in concept to pythagoras, but has to take into account the curvature of the earth for longer distances - so the calculation is quite different to pythagoras - but quite trivial, just a couple of trig functions - this is no doubt the system that sat-navs use to give an instant indication of distances to various points of interest - and evaluate the road distance only when you select a particular point of interest)