By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Newbie seeking relationships

P: 1
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?
Aug 9 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Your junction table doesn't contain the scheduling information I would expect.

EmployeeID, TruckNo., ChassisID, ContainerID, timeIn, timeOut, etc.

EmployeeID and TruckNo. would both be the primary key in this table. ChassisID and ContainerID would both be foreign keys to the primary key of their own tables.

In table design view, when creating each of these fields use the lookup wizard as the data type to find the data for your drop down lists on the form.

If you need to add a new driver, truck etc. Then do it before using this form.

Hope this helps
Aug 10 '06 #2

100+
P: 179
Go back through this forum (Under "Access") about 11 to 12 days ago, and you will see a similar post called "Forms with M:M Relationships". I'm not sure if this will help, but as I said back then, M:M relationships are not recommended by the experts.

Have a look through the replies for this particular post. Maybe you can apply this to your own problem. If you are still unable to solve your problem, let me know. I'll look into it further.

Good Luck.
comteck
Aug 10 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.