One-to-Many relationships

[jk12]

Hi all. I am new to the use of access and have been told that I need to create a one-to-many relationship to be able to have several records from a drop down list saved to one unique number (my primary key).

Is this correct and if so how do I do this? If it is not correct what is the best way to do what I require?

Thanks for helping.

 

[RuralGuy]

If you describe the real world business rule you are trying to enforce then maybe someone could help.

 

[jk12]

I have a table which has a field titled equipment with various types of equipment listed in it and a field titled job number which contains the job numbers. Each job number needs to show multiple equipment types but when i add a new record to add the new equipment access says i can't have repeated job numbers as it is the primary key. It is set as the primary key as it is linked to another table with other data in it. does this make things clearer or am i just making things more complicated then i need to.

 

[RuralGuy]

It sounds like you need at least a [Job] table with [JobID] as the PrimaryKey and an [JobEquipment] table with the [JobID] as a ForeignKey in the [JobEquipment] table. [JobID] would not be the PrimaryKey in the [JobEquipment] table.

 

[neileg]

You actually have a many to many relationship. One job can have many equipment types and one equipment type can relate to many jobs. You need a junction table to implement this many to many.

tblJobs
JobID PK
...
...

tblEquipmentTypes
EquipID PK
...
...

tblJobEquipment
JobEquipmentID PK
JobID FK
EquipID FK

 

[jk12]

Sorry if this sounds stupid but how do i put in a foreign key?

 

[neileg]

The term foreign key refers to a value that is stored in a table as a link to another table's primary key. There's no obvious Access setting for an FK although in the relationships diagram it will be linked to the other table's PK. By convention, you use the same name for the field as a FK as the name used in the table as a PK.