Relationship 1

[akaya]

I have three fields in one table/query ("table 1") that I want to validate against (build a relationship with) the same key in the same related table ("table 2"). 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. I've tried creating separate queries off Table 2 (one for each related field in Table 1) and creating relationships for each field to the queries, but it still returns the same value for each relationship.

What do I have to do to build useable relationships from more than one field in one table (Table1)to the same key field in the same table (Table2)??

 

[BNPMike]

First of all get rid of the relationships as you don't seem ready yet to define your data model.

Then go into the QBE screen and bring in Table 1. Now bring in Table 2 three times. Join Foreign Key Field 1 in Table 1 to the Key of Table 2, then do the same with Field 2 to the second instance of Table 2 and the same again with the third instance. Now you can get matching data for all three fields just as if the tables were all different.

 

[willir]

akaya

It would be much easier to understand your requirement if you could provide specifics.

Table1
Field1
Field2
Field3
... okay so far


Table2
... then I get lost...

I want to validate against (build a relationship with) the same key in the same related table ("table 2").

 

[akaya]

Table 1: Cases
Fields: Key (ID)
Attorney.ID
Client.ID
Expert.ID
etc.

Table 2: People
Fields: Key (ID)
First_Name
Last_Name
Company
etc.

Each of the threee field from Table 1 (Cases) will request different First_Name, Last_Name and Company from Table 2 People).

Appreciate the help, willir.

akaya

 

[willir]

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