Relationships

[CraigBFG]

Is it possible/good practice to have relationships from 2 seperate fields within one table to a single field within another?

Eg
TableUsers
UserId / UserName / User Type
data...
1 / TheCompanyLtd / Developer
2 / TheOtherLtd / Client
3 / Company215Ltd / Client

TableSites
SiteId / SiteName / DeveloperId / ClientId
data...
1 / Site1 / 1 / 2
1 / Site2 / 1 / 2

I don't want to maintain multiple tables of different users if I can help it - redundancy and all that...Hope this makes sense.

Regards
CraigBFG

 

[Pat Hartman]

Using one table is correct. This is similar to using a single table to hold Students and Teachers or Customers and Suppliers or Children and Parents, etc.

 

[CraigBFG]

So, even though all users are maintained in a single table, the PK can be referenced to twice in another?

Then in relationships, you have to enter the same table twice :

t_Users --- T_Sites ---t_Users_1

A developer is responsible for a site.
A client owns the site.
Both developer & client are users of different types.

My test SQL is :

SELECT [tblSites].[SiteName], [tblUsers].[OrgName], tblUsers_1!Orgname AS DevOrg
FROM tblUsers AS tblUsers_1 INNER JOIN (tblUsers INNER JOIN tblSites ON [tblUsers].[UId]=[tblSites].[UId]) ON tblUsers_1.UId=[tblSites].[DevId];

 

[Pat Hartman]

That is correct, you add the referenced table to the query grid twice. You do the same thing when defining relationships in the relationship window. Access appends an underscore followed by a number for each new instance of the same table that is added to the query or relationship window. That allows you to define separate relataionships.