Multi Assignment Solution

[Guest]

I define "Driver" as person who is driving "Truck" which is equipment that is
connected to "Trailer" which is also equipment that contains load that needs
to be transport.

I want to define "Assignment" that will contain AutoNumber Primary Key,
Driver ID Primary Key, Truck ID Primary Key and Trailer ID Primary ID where
only ONE Driver can drive ONE Truck conneceted to ONE Trailer.

When I try to make this table with these 4 columns and all Primary Keys and
when I try to make relationships between tables where Driver ID, Truck ID,
Trailer ID are primary keys and Assingment, Access is only offering 1 to many
relationship and it should be 1 to 1 relationship.

What am I doing wrong?

Thank You

 

[Jeff Boyce]

I may not be understanding your situation...

Are you saying the same driver will ALWAYS drive the same truck with the
same trailer, so that the "Assignment" is permanent? In that situation, I
can imagine a 1:1 relationship.

However, if a different driver could be assigned to drive the same truck or
a different truck could be attached to the same trailer, or ..., then Access
is correct in assuming that your "junction" table (Assignment) uses 1:m
relationships.

That is, the same driver (DriverID) could show up in multiple rows in the
Assignment table. Likewise, the same truck (TruckID) and the same trailer
(TrailerID). In their respective tables, these are primary keys, but in the
Assignment table, they are foreign keys.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John W. Vinson]

I want to define "Assignment" that will contain AutoNumber Primary Key,
Driver ID Primary Key, Truck ID Primary Key and Trailer ID Primary ID where
only ONE Driver can drive ONE Truck conneceted to ONE Trailer.

A table can have only one primary key; that could consist of one or more
fields. In this case you should take one of two options:

1. Use an AssignmentID autonumber field as the primary key, and use three Long
Integer foreign keys, DriverID, TruckID, and TrailerID. Create a unique Index
(using the indexes tool) on these three fields to prevent duplicates.

2. If this table won't be related to further tables, it may be simpler to
leave out the autonumber field and make these three fields the joint primary
key. To do so, include all three fields (as Long Integers), and ctrl-click the
three fields in table design view so they're all selected. Click the Key icon
to make them the primary key.

John W. Vinson [MVP]