Adding more than one response to a field

[SueBK]

I have a table of issues (tblIssues). Each issue has legislation that it must be dealt under. Some issues relate to more than one piece of legislation. Some legislation deals with more than issue. Ideally, therefore, I'd like to have a separate table of legislation (tblLegislation).

Where any issue refers to two (or three) pieces of legislation, can I store that in a single field? Or do I simply need to create a record in tblLegislation for the combined legislation?

 

[MarkK]

This is a many-to-many relationship, in which each single issue might be associated with many pieces of law, and each law might be associated with many issues. This is a textbook many-to-many relationship, and this is a three table solution: tblLegislation, tblIssueLegislation (the link), and tblIssue. Two of those tables are obvious, and in tblIssueLegislation you create a single record for each instance that a single piece of legislation bears on a single issue. At a minimum, tblIssueLegislation looks like ...

tblIssueLegislation
IssueLegislationID (PK)
LegislationID (FK)
IssueID (FK)

Makes sense?

 

[jdraw]

Further to lagbolt's spot on analysis, there is a video on this subject at
https://www.youtube.com/watch?v=7XstSSyG8fw

Good luck with your project