use data from record 2 in record 1

[broecher]

I have a database with location data that looks like this:

Lat, Long, Fix#
A, B, 1
C, D, 2
E, F, 5
G, H, 7

I want to export a file that will be in the correct format for me to generate lines in another app. So I want to make it look like this:

OriginLat, OriginLong, DestLat, DestLong
A, B, C, D
C, D, E, F
E, F, G, H
G, H,,,

Fix# is in the correct chronological order, but since fixes are missing in the record, fix# is not consecutive. I also have fields for the time but I ommited them in the example.

One option i have been trying is to find a way to number them consecutivley, then I could make a second field that is [first]+1 and then make a relationship between them.

Query1
Lat, Long, Fix#, counter
A, B, 1, 1
C, D, 2, 2
E, F, 5, 3
G, H, 7, 4



Query2
Lat, Long, Fix#, expr: [counter]+1
A, B, 1, 2
C, D, 2, 3
E, F, 5, 4
G, H, 7, 5

I used this code to number them: http://www.trevor.easynet.co.uk/AccF...es.htm#counter But the problem is that they are not numbered in the right order. It almost looks random in fact.

If anyone has an idea of how to make the counter number them in order, or how to do this whole thing some other way let me know.

Thanks!

[NTC]

Once you do have the consecutive numbering i.e.
Query1
Lat, Long, Fix#, counter
A, B, 1, 1
C, D, 2, 2
E, F, 5, 3
G, H, 7, 4

then you want to make a new query using this record set (i.e. Query1) - in design grid put the same record set up twice and put the join between them on the 'counter' field - and add all the appropriate fields so that this query runs...but in this step 1 will be joined to 1, 2 to 2 etc.... once that works, then switch to sqlview and modify the join language to include a +1 ....so now 1 will join to 2 (rather than 1)...

hope this helps.

[broecher]

Thanks, Changing the SQL to say -1 sounds a lot easier. Found an easier way to add the counter here:

http://www.vb123.com/toolshed/07_acc...ntercolumn.htm