I'm attempting to computerize traffic logs for a cartage company. (They move all those corrugated shipping containers you see on trucks, trains, and/or ships) This refers to a single truck terminal.
I'm pretty sure I have most of my tables set up properly:
Driver Data - Primary key is their employee ID (Name, CDL#, etc)
Truck Data - Primary key is the co.'s truck number (Lic Plate #, LP State)
Container Data - Primary key is the box number (ICO #, Seal #)
Not sure if these are done as well as they should be though:
Chassis Data (the trailer that the container sits on) - Primary Key is the chassis number, and the FHWA inspection date is the only other item in this table, but that date will change every year or so.
Now here's what I want to do:
There's a many/many relationship between driver and truck, however usually it works more like a one to one as drivers usually drive the same truck. Most (but not all) of the drivers and trucks data are already entered. So I want to be able to have the security officers enter the truck number into a form and have the known drivers of that truck appear in a drop down list with the most probable driver set as the default. However if a truck number or driver's number isn't already in the database, then a sub-menu should allow them to enter the necessary information.
Finally I need to have the date and either the 'time in' or 'time out' recorded for each entry. Not sure if this is best put into its own "Dynamic Data" table or attached onto one of the other tables.
As my old Que Publishing book on Office 97 only skimmed briefly over how to create relationships and the need for a junction table, I simply created a table called "Junction Data" and created copies of each primary key label, and then created a one-many relationship between each table and the junction table.
I then built a basic form (without the sub-menus I described) using the form wizard. However when I tried to access the form, only the labels appeared but no data entry boxes.
I figure I've screwed up in at least a couple dozen places, and I've scoured the internet for a schema similar enough to what I'm looking for but have had little success. Anyone up to helping a clueless n00B figure things out?