My company is updating their computers and our current DOS based software won't work anymore. We're upgrading to Access! Woohoo! Therefore this year, I have been working to learn Access and create the system that everyone will use. We are a trucking company which exclusively hauls US mail. We dispatch drivers out of 9 different cities in 4 states. My coworkers who will be using the system are not very good with computers, so the DOS based system was great for them.
So far, I have Drivers, Post Offices, Vendors, and Purchase Orders forms and tables. They're not done, but started. I'm starting to work on a table(s) and form(s) for the Trips. I've attached a screenshot of what our current Alpha 4 Trips database looks like.
I have a tblPOFacility table which lists all the Post Offices we go to and tblContract which lists all the contracts we have.
The fields I am sure I would like in my tblTrips would be:
TripNumber
TripRate
Contract_ID (FK related to tblContract.ID)
POTripNumber
Trip Title
TimeZone
LoadTime
LoadPO (somehow related to tblPOFacility)
I know I'll need Leave and Arrive times and that will need to be at different Post Offices. It seems my table gets really confusing when I add Time1, Time2, Time3, Time4... Time26, Time27... and then there's the Post Offices... PO1, PO2, PO3... Should I make a TripTimes table? Will I need to make a many to many relationship between tblPOFacility and tblTrips?
I think I'm over-complicating things in my brain and just need someone to say the magic word that makes it all come together. Anyone got that magic word?