Using code and automation to schedule assets...

[wchelly]

I need to develop some code that will schedule and track availablilty of truck trailers based on our shipment schedule and maintenance.

What I would like to do, is develop an Event Procedure that will assign the trailers automatically to shipments based on their availablility.

*Tables* (Basic Elements of them)
Shipment_Schedule_Tbl
ShipmentID
Shipment and Return Dates
Shipment_Number

Maintenance_Tbl
PackageID
Trailer_Number
On_hold (this tells us if the trailer is being held for Maintenance)
Dates

Package_Track
ShipmentID
Trailer_Number

A Union query marries the "shipment schedule" and the "trailer maintenance schedule" so that I can get a listing of dates that the each trailer is "In-Use", "Available", or "On-Hold".

It is unclear to me how to go about automatically assigning these trailers to shipments. I don't see how to do it with a query...seems like I will need to use code. Any ideas and if so can you point me in a direction that will give me some idea of how to go about coding it.

Thanks in Advance!

 

[plog]

You asked more questions (and raised possible issues) than whats capable of being answered based on the data you provided. Overall, we would need more information about how data gets into each table and what each table is for.

One specific issue I have is this:

*Tables* (Basic Elements of them)

What does that mean? Do you have multiple tables with the same fields? Another big issue is that you mentioned a UNION Query which is another sign of a poor structure. I'm suspicious that you don't have the tables set up correctly.

Do you have a single table that holds all your trailers? If so, what is the name of it and what fields are in it?

 

[wchelly]

Tables (BASIC ELEMENTS OF THEM): Means I have multiple columns that I do not want to list here because they are irrelevant.


This table holds all the trailers.
Maintenance_Tbl
PackageID
Trailer_Number
On_hold (this tells us if the trailer is being held for Maintenance)
Dates


THE LONG VERSION: All of the fields listed in this table....
PackageID
Trailer_Number
On_Hold
Package_Type
Location
Container_Comment
Release Status
Condition_Comment
Location_comment
Created_BY
Date_ADDED
Storage_Level


Whenever this table is appended, a copy of the record goes to the history table, so that it will store maintenance history.

 

[wchelly]

In my opinion the union query is unavoidable in this case. Really this is two datbases being combined into one.

This started as a shipment tracking database. We ship packages and we want to know when where and why.

The second database is a package maintenance and package tracking database. There are packages that go on the trailers and in this case the trailers themselves could be considered large packages.

These two databases track different things, but in order to fully track the trailers (and eventually the packages) we must tap into the shipment schedule database. So the union query combines the maintenance history with the shipment history...in combination they provide a full history of where these packages have been and when.