Complex Problem - Building A Single Step Macro - Relying on Artificial Key, Auto Number, Analyzing D

[IGotThis77]

Hi there. I am at a loss. I am looking for some help. I am taking on a data base that is huge. The data was being previously managed in Excel and due to the many different instances, calculations had to be done manually. These had not been managed for years due to the extreme large amount of data to analyze. No one had time. I am at a loss. I have a good foundation. I am, however, stuck. The key has to be artificial as there is no single entity to that is unique to any one part of any field.



Here is the deal. I have one car, making many moves. This car will make many trips and each movement will determine if it is an empty move or a loaded move. The entire objective is to analyze the loaded mileage vs the empty mileage by analyzing the moves for each trip. Each trip is determined by the loaded, empty, loaded status of each movement.

I am using an artificial key to AutoNumber each movement. It is the only way the key will be unique. I then need to look at each cell in the loaded column. When the column goes from >1, then 0, then back to >1, I need to input a trip#, in a different column, the same for every movement, (of that trip or)for that range of cells where the cell value went back to <1, minus the cell that went back to the >1. Everything above that last >1 entry. Basically this is to group each trip number and a new trip does not start until the, loaded trip goes to empty, then to loaded, again.

I am so lost. Is this an array, loop, and If statement? A combination of them all. The need is to build a macro but I can not figure out how to manage this movement. I think i am probably overthinking this. Making it harder than it is. I am rusty at Access. I love working with it. I am just frustrated at this point. Anyone have any good suggestions that might lead to an epiphany on my end??

[Micron]

I then need to look at each cell in the loaded column. When the column goes from <1, then 0, then back to <1, I need to input a trip#, in a different column, the same for every movement, (of that trip or)for that range of cells where the cell value went back to <1, minus the cell that went back to the <1. Everything above that last <1 entry. Basically this is to group each trip number and a new trip does not start until the, loaded trip goes to empty, then to loaded, again.

Not real clear to me. 0 is less than 1?? Perhaps you should use a spreadsheet to set up some data, then copy it and dump it here. I removed my original idea because I figure there is a very good chance it was off the mark as it seemed too simple.

[IGotThis77]

No I used the wrong sign. >1, to 0, then back to >1. This problem has me feeling like, and acting as, a moron. I am sure it is pretty simple. I have the data in a spread sheet but due to the many like instances I in vehicle identification number and other fields, plus the variance of moves to complete a trip, it does not work out in Excel. At least that I have found.

Basically the car is moving loaded when it starts at the first move >1 (miles loaded), when it goes to 0 it is moving empty(miles), and then when it goes back to >1, it begins a new trip. I have another column that could be used that is would be the exact opposite when this is true. I really only want to work with one column in the database to calculate this. Once I can figure out how to make this work it then needs to return an AutoNumbered trip number to the range analyzed for this trip minus the cell it found the car starting the new trip in. If you have an easy solution, that seems to easy, bring it. It probably is what I am looking for. I am struggling with grouping these trips without having to analyze every movement manually and I really want to eliminate the manual analysis.

[Micron]

There are details I'm probably not picking up, such as can the driver start loaded at point A, drop off, go to B, drop off, go to C... before the moves empty from the last place back to home. Or is it one trip loaded A to B, return to A empty? Or you might have to work within the design you have, which is why I suggested a data dump...
**If one trip, A to B (loaded) to B to A (unloaded), why not

tblTrip
TripID	Desc	Dest
1	brown parcel	customer1
2	green parcel	customer2

tblTripDtl
TripID	DteTimeStart	OdomStart	DteTimeEnd	OdomEnd	LoadFlag
1	1/15/15 8:00 AM	25103	1/15/15 9:00 AM	25112	Yes
1	1/15/15 9:00 AM	25112	1/15/15 10:00 AM	25121	No

With this, I think you can get the time duration total per TripID (MaxOfDteTimeEnd - MinOfDteTimeStart) AND overall distance (MaxOfOdomEnd - MinOfOdomStart) AND loaded vs unloaded time AND distance (OdomEnd - OdomStart WHERE LoadFlag <> 0 [or LoadFlag = 0]). Note: some say it is better to compare to 0 rather than use -1. You would input or retrieve the data in a form with the tripID in header and details on a subform datasheet (or split form).

Perhaps this is not at all what you wanted because you have constraints that this won't fit. Sorry if I don't understand the entire scenario.
** as an afterthought, I don't see why this couldn't work for either situation.
I realized afterwards VehicleID would be needed in tblTrip, or if you have several and no table about them, tblVehicles might be a good idea also.

[IGotThis77]

I will do a data dump. I do not have the information at home and need to change some of it. I was pretty vague and this is an instance that most people would not normally run into unless dealing with this type of logistics. A lot of the time of duration is provided. The vehicle makes multiple switches to get from point A loaded to its destination. Or sometimes it does a point A to B and from B to A. Most the time not and it is never a set number of switches it will make. I will post some tomorrow night that might clear up what I am actually talking about.

Thank you.