You may want to read some background material. Litwin's article is excellent...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)
Micro$oft's answer to design and relationships...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database
You seem to want to link the case table to the people table as the attorny, expert, client. Although confusing at first, this is a pretty good way of handling the issue.
What you have is a many-to-many relationship. A case has many people, and people can attend many cases (hopefully as the expert or lawyer and not as the client).
To capture a M:M relationship, you need an intermediary or joiner table, and in fact, this is what you have actually presented.
tblPeople
PersonID - primary key
PersonLN - last name
PersonFN - first name
CompanyCode - foreign key to company
...etc
Discussion: You may have more than one person from the same company. For example, a group of lawyers belong to one company, a group of clients may belong to one company.
tblCompany
CompanyCode - primary key
CompanyName
...etc
tblCase
CaseID - primary key
CaseTitle
Defendent - foreign key to tblPeople
Plantif - foreign key to tblPeople
Ruling
Discussion:
Not sure what else to include here. Things unique to the case.
tblCasePeople
CaseID - foreign key to tblCase
ClientID - foreign key to tblPeople.PersonID
CapacityType - text - Client, Lawyer, Expert
Primary key = CaseID + PeopleID
Discussion:
This is basically your Table2. The difference is that the instead of defining the Capacity or Title as a field, it is defined within the table. This change in design allows
- more than one client to be part of a case
- more than one lawyer to be part of a case
- more than one expert to be part of a case
Now if a person can be part of a case in more than one capacity, the design needs to be tweaked a bit...
Primary key = CaseID + PeopleID + CapacityType
This change would be required if a person can attend the case in more than one capicty such as a client and a lawyer.
One more thing - Case date(s). Since a Case mae be spread out over several days, months, then if you are tracking the case dates, it is actually going to be stored on another table.
tblCaseDate
CaseID - foreign key to tblCase
CaseDate - date
Primary key = CaseID + CaseDate
Revisiting
tblCasePeople and
tblCaseDate...
If you are going to track who attends which case dates, you will have to ponder how. Obviously, the Client will just about always attend. But you may have different lawyers attend, depending on schedule, and experts may attend only once.
One approach could perhaps be...
tblCaseDate
CaseDateID - primary key
CaseID - foreign key to tblCase
CaseDate - date
tblCaseDateAttendee
CaseDateID - foreign key to tblCaseDate
PersonID - foreign key to tblPeople
...Moving on
The values in each of the fields in "Table 1" will be different (each, however, a valid key in "Table 2") and so each should pull back a different value into my query
Perhaps some sample data would help
[tt]
tblPeople
PersonID PersonLN PersonFN CompanyCode
1 White Gandalf Self
2 Baggins Frodo Baggins
3 Gamgee SamWise Baggins
4 Smeagol Gollum Self
5 Sauron Doom
6 Saruman Doom
tblCase
CaseID CaseTitle Defendent Plantif
1 Ownership of Ring 2 6
tblCasePeople
CaseID ClientID CapacityType
1 1 LawyerDefendent
1 2 Defendent
1 3 Expert
1 4 Expert
1 5 LawyerPlantif
1 6 Plantif
[/tt]
Depending on your needs, you may have to tweak the defendent and plantif stuff, but hopefully you have enough info to proceed.
Richard