Hello,
First post, please be nice.
I am looking to see if this would be possible to do in a query. If not a query, I am looking for advice on what direction to go. First off, I am an Engineer for an automotive company. One of our main focuses is to ensure that trucks are scheduled to arrive/depart in a timely manner, while also being 100% utilized. The second part is what I am looking to automate.
The data I have:
• Truck/Route ID *Initial value is not critical to the final result. Data will be calculated based on result of query.
• Volume (Cube/Weight of product) *Critical to the final result.
• Connection Times (Arrival To/Departure From a specific location) *Critical to the final result.
What I am looking for:
• A programmatic solution to assigning (X) volume to variable Truck/Route ID and Connection Time
• (X) volume should represent a fixed value stored on a form or table. This number can be anywhere from 85% to 100%, depending on established targets.
• Most important takeaway is to "cut", or release, a truck when volume reaches the (X) value and assign a Truck/Route ID and Departure Time ("mm/dd/yy hh:nn")
Example of what I am looking for:
• Supplier A delivers 50% volume at 09:00, Supplier B delivers 25% volume at 09:15, Supplier C delivers 25% volume at 09:30, and Supplier D delivers 75% volume at 10:00. Truck/Route ID #1 would "cut" at 09:30, due to there being 100% volume. Truck/Route ID #2 would begin the cut process at 10:00 with 75% volume
• Final dataset would look like (can be wrote to tblTempConnectChart or simply exported from Query to MS Excel);
RouteID¦RouteCutTime¦OverallCube¦Supplier¦SupplierArrivalTime¦SupplierCube
RID01¦01/01/1900 09:30¦100%¦SupplierA¦01/01/1900 09:00¦50%
RID01¦01/01/1900 09:30¦100%¦SupplierB¦01/01/1900 09:15¦25%
RID01¦01/01/1900 09:30¦100%¦SupplierC¦01/01/1900 09:30¦25%
RID02¦¦SupplierD¦01/01/1900 10:00¦75% *Notice there is no Cut Time or Overall Cube. This is caused by the Route ID not meeting the Overall Cube target.
(I apologize in advance for the above table structure. I tried to format, but no dice)
Any help, or guidance, you are able to provide would be greatly appreciated. I look forward to your feedback.
Dave
First post, please be nice.
I am looking to see if this would be possible to do in a query. If not a query, I am looking for advice on what direction to go. First off, I am an Engineer for an automotive company. One of our main focuses is to ensure that trucks are scheduled to arrive/depart in a timely manner, while also being 100% utilized. The second part is what I am looking to automate.
The data I have:
• Truck/Route ID *Initial value is not critical to the final result. Data will be calculated based on result of query.
• Volume (Cube/Weight of product) *Critical to the final result.
• Connection Times (Arrival To/Departure From a specific location) *Critical to the final result.
What I am looking for:
• A programmatic solution to assigning (X) volume to variable Truck/Route ID and Connection Time
• (X) volume should represent a fixed value stored on a form or table. This number can be anywhere from 85% to 100%, depending on established targets.
• Most important takeaway is to "cut", or release, a truck when volume reaches the (X) value and assign a Truck/Route ID and Departure Time ("mm/dd/yy hh:nn")
Example of what I am looking for:
• Supplier A delivers 50% volume at 09:00, Supplier B delivers 25% volume at 09:15, Supplier C delivers 25% volume at 09:30, and Supplier D delivers 75% volume at 10:00. Truck/Route ID #1 would "cut" at 09:30, due to there being 100% volume. Truck/Route ID #2 would begin the cut process at 10:00 with 75% volume
• Final dataset would look like (can be wrote to tblTempConnectChart or simply exported from Query to MS Excel);
RouteID¦RouteCutTime¦OverallCube¦Supplier¦SupplierArrivalTime¦SupplierCube
RID01¦01/01/1900 09:30¦100%¦SupplierA¦01/01/1900 09:00¦50%
RID01¦01/01/1900 09:30¦100%¦SupplierB¦01/01/1900 09:15¦25%
RID01¦01/01/1900 09:30¦100%¦SupplierC¦01/01/1900 09:30¦25%
RID02¦¦SupplierD¦01/01/1900 10:00¦75% *Notice there is no Cut Time or Overall Cube. This is caused by the Route ID not meeting the Overall Cube target.
(I apologize in advance for the above table structure. I tried to format, but no dice)
Any help, or guidance, you are able to provide would be greatly appreciated. I look forward to your feedback.
Dave
