One of my co workers posted a post earlier concerning something similiar and though the theory worked it will not work for what we need it for.
Here is the situation:
We have two tables:
The First Contains Dispatch Info with a new entry put in every time the driver switches loads:
TripNo
TrailerNo
Dispatch Date
DriverCode
Skirts
51455
4587
11/1/2009
DIRKA
0
51465
4547
11/5/2009
DIRKA
0
51568
4578
11/9/2009
DIRKA
1
58465
4578
11/14/2009
DIRKA
0
54686
4685
11/10/2009
MARKC
0
51548
4578
11/15/2009
MARKC
1
The 2nd contains MPG information Date Ranges Differ from table above the MPG query is trigger manually or by a new dispatch:
StartDate
EndDate
DriverCode
MPG
11/1/2009
11/3/2009
DIRKA
12.5
11/3/2009
11/5/2009
DIRKA
11.5
11/5/2009
11/9/2009
DIRKA
14.5
11/9/2009
11/14/2009
DIRKA
16.5
11/14/2009
11/16/2009
DIRKA
10.3
11/10/2009
11/12/2009
MARKC
11.5
11/12/2009
11/15/2009
MARKC
13.2
11/15/2009
11/16/2009
MARKC
17.2
My idea is converting the first table into something like this:
TripNo
TrailerNo
Dispatch Date
DriverCode
51455
4587
11/1/2009
DIRKA
51455
4587
11/2/2009
DIRKA
51455
4587
11/3/2009
DIRKA
51455
4587
11/4/2009
DIRKA
51465
4547
11/5/2009
DIRKA
51465
4547
11/6/2009
DIRKA
51465
4547
11/7/2009
DIRKA
51465
4547
11/8/2009
DIRKA
51568
4568
11/9/2009
DIRKA
51568
4568
11/10/2009
DIRKA
51568
4568
11/11/2009
DIRKA
51568
4568
11/12/2009
DIRKA
51568
4568
11/13/2009
DIRKA
58465
4568
11/14/2009
DIRKA
58465
4568
11/15/2009
DIRKA
58465
4568
11/16/2009
DIRKA
54686
4685
11/10/2009
MARKC
54686
4685
11/11/2009
MARKC
54686
4685
11/12/2009
MARKC
54686
4685
11/13/2009
MARKC
54686
4685
11/14/2009
MARKC
51548
4568
11/15/2009
MARKC
51548
4568
11/16/2009
MARKC
Thus giving each date a full row of info. Then making a "=Date&DriverCode" key.
Then do the same with the MPG Table and make another "Date&DriverCode" Key
We do not know how to do this expansion inside the code. I have wondered if an autogenerate macro would do it. AutoGenerate(date) until a new record #?
I dont know just through things out there.
The end goal is to be able to track the MPG for each driver each day and compare those days he tows trailers with "skirts" to the days he tows other trailers.
If this is long haul trucking data, having multiple dispatches in a single day seems like an inevitability. When that does happen, merely storing dates is ambiguous. You say you trigger a new MPG record for each dispatch. So now we have multiple MPG records and multiple dispatches, all on the same day, with nothing to distinguish between them. Multiple dispatches in a single day is also a problem for your intended goal of one record per day. You would need multiple records per day.
The simple solution to the ambiguity problem is to store timestamps, not merely dates. However, it will not be possible to create a separate record for every millisecond in the day. Perhaps it is impossible to have more than one dispatch in an hour, and you could generate hourly records, but that's still a whole lot of redundant data.
So I think we need to get back to this:
The end goal is to be able to track the MPG for each driver each day and compare those days he tows trailers with "skirts" to the days he tows other trailers.
See the attached for a way to turn your raw data into a single combined table with records that support this analysis. If I've misunderstood the requirement, please clarify.
I should also note that your example data is insufficient to actually calculate the average MPG. I weighted it by days driven, since that seemed to be what you were after, but this is incorrect. The correct way to handle it is to store both the miles driven and the gallons of gasoline used, then simply sum(Miles)/sum(Gallons). Is this information available?
Mind you, I really need to get back to my own work, so I'm not sure when or if I might be able to get back to this. Ah, well. Hopefully something I've said was helpful.
