Relationship to Multiple Fields

[djwatts]

I am working on a housing application, I have two tables (of many) one is called contracts and contains four fields; ContractNo, Surveyor, QS, COW.

Surveyor, QS & COW all relate to the SurveyorID in the Surveyor table.

When I design a form I can use combo boxes etc to display the surveyors name, but when I build a query/report I can only get the name for the Surveyor field and not the other two. Do i need to use expressions or customise the inner joins in SQL???

Any help appreciated

Regards

DW

 

[lespaul]

Surveyor, QS & COW all relate to the SurveyorID in the Surveyor table.

Then all you should be storing in your Contracts table is Surveyor ID.

Contracts                         Surveyors
ContractNo (PK)                   SurveyorID(PK)
SurveyorID (FK)                   Surveyor
                                  QS
                                  COW

Then your query becomes:

SELECT ContractNO, Surveyor FROM Contracts INNER JOIN Surveyors on Contracts.SurveyorID = Surveyors.SurveyorID

Anytime you have data duplicates in your tables you haven't finished normalizing. Check out The Fundamentals of Relational Database Design



Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[djwatts]

Sorry I don't think I made it that clear,

Each contract can have a different Surveyor, QS and COW

(Sample Records)

[Contracts]
ContractNo (PK) = 1001
Surveyor (FK) = 01
QS (FK) = 05
COW (FK) = 08

[Surveyor]
SuveyorID (PK) = 01
Name = "Dan Watts"
---------------------
SuveyorID (PK) = 05
Name = "John Smith"
---------------------
SuveyorID (PK) = 08
Name = "James Connolly"


The fields in [Contracts] link to SurveyorID

Thanks

Dan

 

[lespaul]

Ok then:

SELECT ContractNO, A.SurveyorName, B.SurveyorName, C.SurveyorName FROM
Contracts
INNER JOIN Surveyor As A On Contracts.SurveyorID = A.SurveyorID
INNER JOIN Surveyor As B On Contracts.SurveyorID = B.SurveyorID
INNER JOIN Surveyor As C On Contracts.SurveyorID = C.SurveyorID


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

SELECT ContractNo, S.Name AS S_name, Q.Name AS Q_Name, C.Name AS C_Name
FROM ((Contracts A
INNER JOIN Surveyor S ON A.Surveyor = S.SuveyorID)
INNER JOIN Surveyor Q ON A.QS = Q.SuveyorID)
INNER JOIN Surveyor C ON A.COW = C.SuveyorID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

copy and paste error! PHV's solution is correct.

les