I need to have a database developed for a truck shipping company. They have approx 20 managed drivers, and 4 salary drivers. Data entry forms should be user friendly and quick to navigate. Keyboard entry only options.
*** Must be multi-user capable, run on a windows xp workstation, and be easily updated or modified to handle new requirements.
First, each truck and driver need to be tied together, with dates and assignment history for trucks to drivers. All data will be by truck number, but payment information will be by driver.
Gas purchases entry will occur daily, using a quick entry form to include the date (default to current day), trip #, invoice #, truck #, gas cost, advance (optional), city and state.
The trip details will not be entered until it's completed. When it's completed, a bill of lading will be entered, using again the trip and truck number(single byte), date, Line Haul pay($), fuel surcharge($) Lumper, Layover, Extra Pickup/Drop off, Misc Expenses, trailer rental(boolean[y/n]), gas card surcharge(boolean[y/n]), Dead head miles.
A split load may occur and if is required if the trip requires additional drivers be paid, and may include as many as four additional. I am open to suggestions for split load handling. It may be split by percentage or flat rate for a given load, but the split should be tested to prevent paying out more than is collected.
Miscellaneous charges form to allow entry and requires the following fields: date(default today), truck #, category (drop down pull from table - Truck payments, License/Registration fees, Insurance payments, Permits, taxes, other), amount ($).
The weekly settlement breaks this out showing only the completed trips since the last settlement.
Each week settlement needs to be generated. When generated, it should ask if any additional should be withheld and be entered as a percentage or dollar amount. Column A will show trip # with gross pay, expenses, fuel surcharge, layover, extra PU/DO, and lumper items for EACH trip. If lumper, extra PU/DO, or Layover charges are $0, I prefer not having it show on the settlement. There will often be several completed trips on a settlement, it needs to list and itemize each.
Right column would show:
Brokerage fee (17% of all col A EXCEPT Fuel Surcharge and lumper),
Trailer rental (6% of of all col A EXCEPT Fuel Surcharge and lumper),
Gas Card usage (3% of of all col A EXCEPT Fuel Surcharge and lumper), Advances, TCH Fee, and additional witheld.
I would like the form to be previewed, and modified, accepted or denied before finalizing. Once accepted, move the completed trip information into a settled trip table with fuel info and all other trip details with the settlement being saved as accepted.
Each column would be totaled, show Total Col A minus Total Col B, then payment to be made to driver. Each settlement then needs to be stored until archival.
We would like YTD statements, showing trip numbers completed, totals from the trips, totals of expenses, totals paid
An annual archiving of completed trips and processed settlements needs to be made at the end of each calendar year. It should be easily retrievable, but not modifiable.
When archived at end of year, trips completed but not settled should remain and carry over. Trips in progress should also carry over to the next year (simply put - don't archive). Suggest a boolean field for each trip indicating settled, which becomes checked when the settlement for that driver is run. For split trips, suggestions welcome to prevent multiple payments to a single driver.
Trip number format is a 7 digit number, 1 digit year, 2 digit employee number of originating driver, 4 digit invoice number.
+++ The ability to fix, update, and grow this database is imperative. I am not looking for the cheapest bid, but I will require good work. If you are willing to make minor updates or changes, please state it. If not, list additional costs to do so. Please also list suggested database platform or method of implementation.