Hi everyone,



I was just wondering if anyone could offer a thought as to how to 

approach "circular relationships" -- simply put, how to set up 

relationships amongst tables that all refer to the same field.



Oh, that's tough to figure out, the way I've stated it, so let me give you 

the design I have:



I have 3 tables about Employees, and they are:



tblEmps (list of Employees)

tblTeams (list of Teams on which employees are assigned)

tblManagers (list of Managers for Employees, containing specific Mgr info)



And here's a bit of what each table contains:



tblEMPS:

--------------------------

EmpID (AutoID)

EmpNum (Actual Employee Number, Unique Index)

EmpName (Name of Employee)

TeamID (Lookup to TeamID field in tblTeams)

--------------------------

tblTEAMS:

TeamID (AutoID)

TeamNum (Actual Team Number, assigned by Finance)

TeamName (Name of Team)

MgrID (Name of Manager -- Lookup to MgrID field in tblMGRS)

--------------------------

tblMANAGERS:

MgrID (Name of Manager -- Lookup to EmpID field in tblEMPS)

AskForInfoID (Name of Contact for Manager -- Lookup to EmpID field in 

tblEMPS)

OtherStuff (just a few fields that are specific to Manager Info)

DirectorID (Manager of the Manager -- Lookup to EmpID field in tblEMPS)

--------------------------



So, my question is, when I am setting up relationships, should I just draw 

lines between the 3 Primary Tables?  Or should I set up "sub tables" ... 

for example, bring out a "tblEMPS_1" and draw a line between its EmpID and 

the DirectorID field in tblMANAGERS, and a "tblEMPS_2" that would have a 

line between its EmpID and the AskforInfoID in tblMANAGERS?



I'm fairly content with the structure I've designed for this database, but 

if you think the way I've broken out my tables is just plain silly, feel 

free to comment on that fact.  It sort of looks like it could be improved 

(though it will work fine as is), and I'm happy to hear any ideas that 

will help me to achieve a sturdy foundation! 



Many thanks,

Susan

I believe that the two options are functionally equivalent, as far as being

able to do referential integrity is concerned.  So I would say this is just

a personal preference thing & you should do whichever looks least goofy to

you.



Personally, I tend to get confused by the little "_<n>"'s that access

appends to the end of the second, third, fourth, instance of a particular

table as you add them to the relationships view, so I would go with multiple

lines drawing off a single instance of a table.  But you may feel

differently...



Also FWIW, if I understand the tables here, one other way to go would be to

include a MgrID field in your tblEmps table, and link it to the EmpID field

in tblEmps.  That way you can trace the hierarchy of even the low-level

employees to their respective managers if need be.  Your big boss gets a

null in that field, everybody else has a manager.  Managers are employees

whose IDs appear in a SELECT DISTINCT on tblEmps.MgrID.  Note that if you do

this & you want to enforce RI (every MgrID must appear in the EmpID field),

you *have* to add more than one instance of tblEmps to your relationships

view.



HTH,



-Roy



-----Original Message-----

From: Susan Henesy [mailto:susan.henesy@w...]

Sent: Thursday, June 28, 2001 2:24 PM

To: Access

Subject: [access] Circular Relationships!





Hi everyone,



I was just wondering if anyone could offer a thought as to how to 

approach "circular relationships" -- simply put, how to set up 

relationships amongst tables that all refer to the same field.



Oh, that's tough to figure out, the way I've stated it, so let me give you 

the design I have:



I have 3 tables about Employees, and they are:



tblEmps (list of Employees)

tblTeams (list of Teams on which employees are assigned)

tblManagers (list of Managers for Employees, containing specific Mgr info)



And here's a bit of what each table contains:



tblEMPS:

--------------------------

EmpID (AutoID)

EmpNum (Actual Employee Number, Unique Index)

EmpName (Name of Employee)

TeamID (Lookup to TeamID field in tblTeams)

--------------------------

tblTEAMS:

TeamID (AutoID)

TeamNum (Actual Team Number, assigned by Finance)

TeamName (Name of Team)

MgrID (Name of Manager -- Lookup to MgrID field in tblMGRS)

--------------------------

tblMANAGERS:

MgrID (Name of Manager -- Lookup to EmpID field in tblEMPS)

AskForInfoID (Name of Contact for Manager -- Lookup to EmpID field in 

tblEMPS)

OtherStuff (just a few fields that are specific to Manager Info)

DirectorID (Manager of the Manager -- Lookup to EmpID field in tblEMPS)

--------------------------



So, my question is, when I am setting up relationships, should I just draw 

lines between the 3 Primary Tables?  Or should I set up "sub tables" ... 

for example, bring out a "tblEMPS_1" and draw a line between its EmpID and 

the DirectorID field in tblMANAGERS, and a "tblEMPS_2" that would have a 

line between its EmpID and the AskforInfoID in tblMANAGERS?



I'm fairly content with the structure I've designed for this database, but 

if you think the way I've broken out my tables is just plain silly, feel 

free to comment on that fact.  It sort of looks like it could be improved 

(though it will work fine as is), and I'm happy to hear any ideas that 

will help me to achieve a sturdy foundation! 



Many thanks,

Susan

Hi Roy,



Thanks so much for your response!



So, if I understand you correctly, I can create multiple links to one 

field, and it won't hurt referential integrity.  Doing this is the same as 

as bringing out multiple instances of a Primary Table, and  it's just a 

matter of visual aesthetics.  If this is true -- GOOD!  I would much 

rather have several tables linking to the same field in one table, than 

bring out a bunch of "_<n>" tables.  Things just look mighty crowded that 

way.



I also liked your idea of moving the MGRID info to tblEMPS -- and 

understand why I would then REQUIRE a tblEMPS_1 in order to create a 

relationship!



What I've actually done is deleted tblMANAGERS, and moved all that info to 

tblTEAMS -- because everyone has some kind of Finance Number attached to 

their "team" (even if they're a Senior VP) -- so, it's more efficient to 

track management by this module than by individual employee.



Now I just have one more question!  It's alright to hook several tables up 

to one field in one table in the RELATIONSHIPS window.  BUT...when I am 

creating a query, then I find I *must* have multiple instances of a table 

("tblEMPS_1", "tblEMPS_2"), if I want the right names to come out based on 

the ID Numbers.  This is what made me queasy about hooking several tables 

up to one field to begin with.  Have you (or anyone else) got any ideas on 

why that is, and how all this stuff (relationships, I mean) works?  And 

by, how do relationships work, I guess what I mean is, why is it alright 

to hook multiple tables up to one table in Relationshps??



Dizzy from trying to fathom the logic,

Susan

Hey--happy to be of service.  Please see responses below.



-----Original Message-----

From: Susan Henesy [mailto:susan.henesy@w...]

Sent: Friday, June 29, 2001 10:44 AM

To: Access

Subject: [access] RE: Circular Relationships!





> Hi Roy,

> 

> Thanks so much for your response!

> 

> So, if I understand you correctly, I can create multiple links to 

> one field, and it won't hurt referential integrity.  Doing this 

> is the same as as bringing out multiple instances of a Primary 

> Table, and  it's just a matter of visual aesthetics.  If this is 

> true -- GOOD!  I would much rather have several tables linking to 

> the same field in one table, than bring out a bunch of "_<n>" 

> tables.  Things just look mighty crowded that way.



I am nearly positive this is true--but you should test it to be sure.



> I also liked your idea of moving the MGRID info to tblEMPS -- and 

> understand why I would then REQUIRE a tblEMPS_1 in order to 

> create a relationship!



Hee-hee--I'd love to take credit for that, but I was just remembering it.

It's a pretty common example of a so-called 'recursive relationship'.



> What I've actually done is deleted tblMANAGERS, and moved all 

> that info to tblTEAMS -- because everyone has some kind of 

> Finance Number attached to their "team" (even if they're a Senior 

> VP) -- so, it's more efficient to track management by this module 

> than by individual employee.

> 

> Now I just have one more question!  It's alright to hook several 

> tables up to one field in one table in the RELATIONSHIPS window. 

> BUT...when I am creating a query, then I find I *must* have 

> multiple instances of a table ("tblEMPS_1", "tblEMPS_2"), if I 

> want the right names to come out based on the ID Numbers.  This 

> is what made me queasy about hooking several tables up to one 

> field to begin with.  



Hmmm... I'm not completely sure I understand this question.  But maybe this

will address your question--when you add a second instance of a table to an

access query design view, access auto-generates an alias for that table for

the purposes of the query--by appending a _<n> to the end of the table name.

So for instance, if you add tblEmps twice and then look at the SQL view of

the query, you'll see something like:



SELECT ...

FROM tblEmps INNER JOIN tblEmps as tblEmps_1 

ON tblEmps.EmpID = tblEmps_1.MgrID ;



But this alias is changable--you can edit this to say, for instance:



SELECT ...

FROM tblEmps as Employee INNER JOIN tblEmps as Manager 

ON Employee.MgrID = Manager.EmpID ;



You can also alias fields.  So if you wanted to see employee names along

with the names of their managers, you'd do something like:



SELECT Employee.EmpName as EmpName, Manager.EmpName as BossName

FROM	tblEmps as Employee LEFT JOIN

      tblEmps as Manager

ON    Employee.MgrID = Manager.EmpID ;



(Note the left join so that you get the Big Boss on the list as well, along

with a Null for his BossName.)



> Have you (or anyone else) got any ideas on 

> why that is, and how all this stuff (relationships, I mean) 

> works?  And by, how do relationships work, I guess what I mean 

> is, why is it alright to hook multiple tables up to one table in 

> Relationshps??



I'm not sure what to say other than "because that's the way access (and SQL

server, and ORACLE, and...) works". 8^)  Why shouldn't you be able to hook

multiple tables up?



Hope that helps.



-Roy