design tables & relationships for small transportation company

[moalmasri]

Hi there, This is my first time here, I am a fan of Access and have some information about it, I have a small transportation company that hauls loads through brokers and delivers them cross state lines, I have 8 trucks, I am trying to design a small access database to keep track of my trips, drivers, revenues, cost per truck...........etc. any ideas will be appreciated. I already designed some tables and forms, however, I think with some help I can do a lot better, if anyone is interested in helping me with this project, please let me know and I will contact you and submit a copy of the tables I already did. I am having a problem with how to deal with partial loads and how to figure out the whole thing about partial loads. Currently I have the following tables:
Trip Table, Load Table, Load details table, drivers table, trucks table, shippers table, receivers table, customers table (that represents the brokers whom I book my loads through), Expense table, maintenance table for the trucks, vendors table that represents where I buy parts for the trucks, and zip-code table to auto populate city & state for shippers and receivers, trailers table( because the trucks I have each can pull a different trailer 18 wheeler. Please let me know if you can help, I really need this to work. Once again I appreciate any help.


Sincerely Yours
Mo Almasri

[Micron]

I sense that what you have is like having all the wood you think you need to build a house - but no architectural drawings. The problem with Access is that it's easy to jump in and start creating things without a clear plan, and there is a steep learning curve if you want something that works well for you. I suggest you review some or all of these links and maybe bookmark the ones that you're not ready to read. If you're ahead of where I think you are, no harm done. One clue is that you didn't mention that you have any queries and forms, which suggests you intend to work solely against your tables (not a great idea). I borrowed the links between *** from Orange. I presume you're looking for help to build something rather as opposed to looking for contracted service, so this forum is a great place to start. However, it helps if you can talk the talk and do the bulk of the work.
***
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html
***
you might find a data model for what you're doing here
http://www.databaseanswers.org/data_models/

http://www.fmsinc.com/free/newtips/primarykey.asp
http://www.access-programmers.co.uk/...d.php?t=225837
http://allenbrowne.com/AppIssueBadWord.html
http://access.mvps.org/access/general/gen0012.htm
http://www.utteraccess.com/wiki/index.php/Autonumbers
http://access.mvps.org/access/general/gen0025.htm

[ranman256]

since Im not sure of your business methods , I guessed a table strucure

Trip Table,
--------
TripID (auto)
TruckID
TrailerID
DriverID
ShipperID
CustID
RecvID
LoadID
Destination
DepartDate
ArriveDate



Load Table,
--------
LoadID (auto)


Load details table,
LoadID (long)
Contents


drivers table,
--------
DriverID (auto)
FirstN
LAstN
LicenseNo


trucks table,
--------
TruckID (VIN) not auto



shippers table,
--------
ShipperID (auto)
FirstN
LAstN


receivers table,
--------
RecvrID (auto)
FirstN
LAstN


customers table
--------
CustID (auto)


Expense table,
-----------
ExpID (auto)


maintenance table for the trucks,
------------------
MaintID (auto)
TruckID


vendors table
VendID (auto)
FirstN
LAstN
phone


Remember , you only need relationships for Parent/Child tables *(like tLoad & tLoadDetails even tho I dont know what this holds)
dont waste time w relationship for zipcodes and other 'filler' tables.