Logic For Access Program

[smartlikestick]

Hey All:

I'm writing a access database to deal with engineering surveys. The idea is that it will calculate coordinates and elevations of known stations based on a table which has control points.

To sum it up easily, my database will have two tables. The first table, tblControl, will house all of the known coordinates, and associate them to a Station. A station is essentially the linear measurement from one point to another. For example, the first point on a line would be Station 0, and as you traveled towards the next point, the Station number would increase based upon the distance traveled.

The second table, tblStation, will be comprised of known stations. I need the database to compare each record in tblStation against the values in tblControl It needs to find the Station number in tblControl closest to the Station number in tblControl, without going over it.

Any ideas on how to do this?

Thanks

 

[MarkK]

The first table, tblControl, will house all of the known coordinates, and associate them to a Station.

What does it mean that a control is 'associated to' a station? How does this look in respect to key fields that exist in these tables?

I need the database to compare each record in tblStation against the values in tblControl It needs to find the Station number in tblControl closest to the Station number in tblControl, without going over it.

Are these items not already associated as per the above? Also, you don't need to check every record. You can return all values less than a given value and then sort descending. After that the first item in the list satisfies 'closest to, without going over.'

But more generally, it doesn't seem to me that you've supplied enough information about the problem for anyone to suggest to you functional ways of modeling it.

 

[RossWindows]

I've seen AMAZING things done with Access in regards to surveying, so yes I'm sure it can be done.
The trick is explaining it to us regular folk who don't wrap our heads around coordinates on a regular basis.

 

[gemma-the-husky]

linear measurements are easy

if you have 2 lat/longs, its easy to calculate the distance - just google for a function

the calculation is actually a calculation of the fraction of a great circle distance round the earth (a minute - i think - it may be a second - of great circle distance is a nautical mile) although it doesnt take altitude into account. just a bit of trig with sins and cosines - fascinating really.

 

[RossWindows]

Right you are. I guess what I meant is that I'm not used to working with these types of database models so I might be a little slow on the uptake.
Like LagBolt said, I don't feel that I fully understand Smart's model.