Grouping geographic points along a line based on their distance from one another

[Bdowns]

I conduct ecological surveys at sea.

I have a table of Navigation data with geographic coordinates logged on a one second interval during survey lines

The table has a field for Timecode (hh:mm:ss); transect ID, and Latitude and longitude. I also have the lat/lon in UTM format (northing and easting in meters) from which I can calculate the distance between points using the distance formula.

For statistical sampling purposes, the transect lines have to be divided into 100 meter segments. I would like to add a field named [segment] and populate the field with segment_ids so that I can group the records into 100 meter segments. Because the boat changes speeds and stops often, I cannot simply use time to break the transects into segments based on distance.



Any ideas about how I can populate the segment field to divide each transect into 100 m segments. I would like to populate the field with the name of the transect and sequential #'s (e.g. TranA_1, TranA_2, TranB_1, TranB_2...)

thanks!

[June7]

Not sure a query could do this. I am sure a VBA procedure could. Want to provide sample data?