473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Newbie seeking relationships

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
2 1456
MMcCarthy
14,534 Expert Mod 8TB
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
comteck
179 100+
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

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

Similar topics

2
by: Max | last post by:
Hi. I really hope someone can help me. Going slowly insane with this problem. I have a two Access 2000 databases. One is the backend containing tables and some admin queries. The other is the...
7
by: davegb | last post by:
I'm totally new to relational database design. My boss has asked me to create a database of information on the employees in our group. Seemed to me like a simple application to learn the ropes. A...
5
by: clintonG | last post by:
I'm looking for documentation and would not turn my nose up to any code from anybody who thinks they are good at the design of an algorythm that can be used to generated a hierarchical relational...
45
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see...
9
by: ljh | last post by:
Wasn't there a report component that you could use in a VB/VB.Net app that would allow end users to create thier own reports to save and run? I swear I remember seeing such a thing....but I can't...
5
by: Kosmos | last post by:
Hey :) hopefully someone can help me with this...I decided to take on the task of programming an access database for my legal co-op/internship...I'm studying law and music production on the...
13
by: ARC | last post by:
Hello all, Prior to going live with my app, I have questions on relationships theory. My prior app was done in Access 97, and I did NOT use relationships at all. I have 65 tables in my...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.