Identifying Connected Track Mileage

[Mark Richardson]

Geogis Id ELR TRACKID1 START MILES FINISH MILES
1 AAV 1100 29.0110 29.0154
2 AAV 1100 29.0154 29.0215
3 AAV 1100 29.0215 29.0550
4 AAV 1100 29.0550 29.0638
5 AAV 1100 29.0638 30.0000
6 AAV 1100 30.0000 30.0001
7 AAV 1100 30.0001 31.0000
8 AAV 1100 31.0000 31.1694
9 AAV 1100 31.1694 32.0055
10 AAV 1100 32.0055 35.0792
11 AAV 1100 35.0792 35.0875
12 AAV 1100 35.0875 38.1003
13 AAV 1100 40.0935 40.1254

I am trying to extract the start and end miles for each section of track, within this table. Here is only a small bit of it. There are 375,134 records. I need to be able to identify all the mini sections of TrackID for each ELR. As you can see to identify whether something is connected The start mile should be equal to the end mile of the previous record. I am trying to get access to recognise these patterns if someone enters a specific mileage, for example 31. It should recognise that 31 is contained within the AAV 1100 section of track starting at 29.0110 and ends at 38.1003. Could anyone shed some light on if this is possible. I fear this is too advanced for my mere average brain. Or i could just be being stupid.

Thanks
Mark

 

[crosmill]

I'm a little bit confused about what your trying to do but I think LEFT([START MILES], 2) might be of help to you.

This would return the frist two numbers of that column, am I on the right lines?

 

[Mark Richardson]

Yes that is what i am trying but i am struggling to write the code which makes access pull off the start mileage and end milage for specific length of track. The extract of the table in my initial post shows how the overall Track ID sections are split up into tiny sections. I need to be able to identify the whole section start mileage and end mileage to identify an overall length, by telling access that when the start milage is no-longer equal to the previous entry end milage, then that is the end of the overall section. For example ELR: AAV, TrackID 1100. I am unsure whether this is possible and thought i may try posing this to see if anyone had any suggestions. I welcome anything else you can add.

Cheers
Mark

 

[dcx693]

This is not your typical easy data problem in Access, but I can help point you in the right direction. The relationship you're trying to use is a variation of the "classic" employee-supervisor relationship. The trick is to relate the table to itself.

I'll show an altered simplified version of your data table a bit so you can see it more clearly.

Start  Finish
29.011    31.50
31.500    32.111
32.111    38.1103
40.000    41.001
You want to compare the Start numbers to the finish numbers.

When you join tables in Access queries, you join them usually based on which fields have the same values - there's the key. In your Access query, add the data table to the query view more than once. Then join the finish field from one table to the start field of another, and so on. Take a look at the picture I've uploaded to see an example.

Now, I don't have it worked out how to show only distinct sections that don't appear within other sections. You'll see, if you recreate the query example, that line two is already inside line one. And the other problem is that, if you have a section that is broken up into 10 pieces, you'll have to add the table to the query 10 times! A bit of work when it's 10 tables but unmanageable when it's 100.

Ultimately, I think this problem is best solved through VBA code. You just need a loop that goes through each record, checks to see if the finish number of the previous record is the same as the current start number and piece them together. Then write the start and end results to a table.

 

[dcx693]

Here's the sample query output.

 

[Mark Richardson]

Cheers, hadn't thought of having self join but i do agree i need to use VB. Not overly great at it but hey if you don't try you'll never learn.

Mark

 

[crosmill]

OK I finaly got where you want to go, I think

Another possible solution

Create new columns NextStartMiles, NewTrack, NewTrackID

Create (and modify) this query

UPDATE Table1 AS t
INNER JOIN Table1 AS n
ON t.ID = (n.ID + 1)
SET n.NextStartMiles = [t]![StartMiles];


You'll have to do it in SQL the Design View cna't undersatnd it.

This updates NextStartMiles with the Start Miles from the next record, you can then compare Finish Miles with NextStartMiles and give it a True or False vlaue.

I'm working on generating a unique track ID based on this, but let me know if I'm barking up the wrong tree with this before spend the whole afternoon on it.

 

[Mark Richardson]

Thats is what i am after.
Thanks for any help you can provide. Much appreciated.

 

[dcx693]

crossmill, your code is clever, but won't it produce the same type of result as the self-join query which I proposed?

I think you might run into one of the same problems I did which was: There may be a variable number of track lengths that need to be added to create a contiguous stretch. How will your query know how many start/finish times to piece together? You can do it a large fixed number of times and it won't affect the results since you will be testing if the finish time equals the start time of the next, but I always like to produce solutions that go through a minimum of steps.

 

[crosmill]

I'm working on some VBA that will increament a varaiable every time the finish and next start miles don't mach, this will produce a uniqe id number for each track, which I believe is the ultimate goal.......?

 

[Mark Richardson]

Yes, thats correct.

 

[crosmill]

OK I think I've worked a solution but I can't test it, I seem to have lost one my reference libriarys. It won't let me Dim the db

Give this a go anyway

Dim db As Database
Dim rs As Recordset
Dim TrackID As Integer

Set db = CurrentDb()
TrackID = 1

Set rs = db.openrecordset("table1", dbOpenTable)

While Not rs.EOF
If rs("Finish Miles") = rs("NextStartMiles") Then
rs("TrackID") = TrackID
Else
TrackID = TrackID + 1
rs("TrackID") = TrackID
End If
Wend

rs.Close

 

[crosmill]

Finally got it to work

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim TrackID As Integer

Set db = CurrentDb()
TrackID = 1

Set rs = db.OpenRecordset("qrytable1", dbOpenDynaset)



While Not rs.EOF
rs.Edit
If rs("FinishMiles") = rs("NextStartMiles") Then
rs("TrackID") = TrackID
Else
TrackID = TrackID + 1
rs("TrackID") = TrackID
End If
rs.Update
rs.MoveNext
Wend

rs.Close

 

[Mark Richardson]

THat works great. Cheers.
The only problem is that when it is run the trackid increases on the very last mini section of the Track.
When you reach the end of a link, the mileages will not be the same but i need the code to recognise that and still enter in the old trackid. For example for AAV the link finishes at 38.0456 miles and the computer see's this because the StartNextMile value is different 40.0345. At present it is therefore increasing the track id by one for the record. In reality it should still be the same as it is still in sequence with the previous record. I need the code to recognise that where the mileage are different the trackid does not need changing till the record after. Hope this makes sense.

 

[crosmill]

So it increments the id for the last link of EVERY section of track, or just the very last one in the whole recordset?

 

[Mark Richardson]

Of every little section. In basic terms the final piece of the ELR Section is being numbered as a new track section. It is very difficult to explain. Cheers for any help.

 

[Mark Richardson]

Right sorted that. Rather than comparing the finish mile with next Start Mile. I have compared the Start Mile with the previous finishMile. Cheers for help

 

[crosmill]

iI tihnk could also have just swapped a couple of lines around so it updates before the increment

While Not rs.EOF
If rs("Finish Miles") = rs("NextStartMiles") Then
rs("TrackID") = TrackID
Else
rs("TrackID") = TrackID
TrackID = TrackID + 1
End If
Wend

 

[amincomputer]

Halo Guyz,

My name is Amin. I am eager to develop a db for our charity organization based in Kenya to track its 10 vehicle's fuel consumption with mileage. The follwoing are features of the db.

I have table of 15 drivers and I have atable of 15 car plates

I need to design an Access database that track fuel mileage consuption.

I think i must have aform that have the follwing field.

1. <LI itxtNodeId="380" itxtHarvested="1">FuelID(autonumber) <LI itxtNodeId="379" itxtHarvested="1">Drivers_Name ...........(Add addition Buttion for a new driver ) <LI itxtNodeId="378" itxtHarvested="1">Vehicle_Plate_Number...........(Add addition Buttion for a new Car Plate ) <LI itxtNodeId="377" itxtHarvested="1">Vehicle_Type......(Must update tself from Vehilce plate number. I.e when a KX 10 is entered, the vehilce type must automatically display ''Nissan Hard Boby in the vehicle type field.its not data entry field. <LI itxtNodeId="376" itxtHarvested="1">Volume of fuel loaded on each vehicle <LI itxtNodeId="375" itxtHarvested="1">Volume of fuel Reserves...( The reserve is 20,000 lts) <LI itxtNodeId="374" itxtHarvested="1">Volume of fuel balance(reserve) <LI itxtNodeId="373" itxtHarvested="1">Car starting mileage...( each vehicle with its own starting mileage) <LI itxtNodeId="372" itxtHarvested="1">Current mileage..( data entry field. must return the previous mileage number to current for each vehilce) <LI itxtNodeId="371" itxtHarvested="1">Fuel consumed.........(subtract start milage -current milage divide by fuel loaded) <LI itxtNodeId="370" itxtHarvested="1">Date fueled..(pulled from system date)
2. Display who loggged on and computer acount name for accountability