should this be many-to-many?

[Milothicus]

note: all following table/field names have been changed to protect the sanity of the reader.

I have a main table, which includes 3 potential positions for a person to hold:
pos1, pos2, pos3. each field is linked to the same table, which holds a list of people.

in my back end relationship screen, there are 3 'copies' of the 'people' table, to avoid ambiguous joins.

in a front-end query, i had to 'show' 2 extra copies of the table and recreate the joins....

so, if you have one table that's referenced for 3 fields in a main table, is that many-to-many? or is it better to show 3 copies of the lookup table and have 3 one-to-many relationships?

 

[neileg]

Sorry but this doesn’t look right at all. I can’t see any reason to need three copies of the people table.

Now it’s not clear from the question if the people can hold more than one position at the same time, so I’ll assume they can’t, but I’ll assume they can hold different positions at different times.

Three tables then:

tblPerson
PersonID PK
LastName
FirstName
Etc

tblPositionHeld
PositionHeldID PK
PersonID FK from tblPerson
PositionID FK from tblPosition
StartDate
EndDate
Etc

tblPosition
PositionID PK
PositionDescription
Etc

You’ll have a many to many relationship between tblPerson and tblPositionHeld and another many to many between tblPositionHeld and tblPosition.

Does this work for you?

 

[Milothicus]

more detail than i need. each record pertains to a project, so for each project, i need each position filled. doesn't matter if one person is in more than one position even within a project. there are only about 7 or 8 people who can fill the positions, and their info is in a separate table, i just need to associate a person for each job. it works fine as 3 one-to-many relationships, if i show 3 copies of the people table (it's only one table, referenced 3 times)in my relationship windows. i'm just wondering if this is a situation where a many-to-many relationship is what i need.

it seems to me it's not, because one job will have one person for pos1, one person for pos2 and one person for pos3, not multiple people for one position. but i'm still learning, so i'm not sure if this is correct.

i know i'm terrible at describing these things, so i'll try another angle, just in case....

in my people table, i have a primary key (call it personID) and all of the person's info....

in my main table, i have 3 foreign keys (pos1PersonID, pos2PersonID, pos3PersonID) that all relate to personID in my people table.

in the relationships window, does that indicate a many-to-many? or 3 one-to-man(ies) with people, people_1, and people_2?

any clearer?

 

[ecniv]

3 "1 to many"s

Also in the query (when you decide to make one) you can alias the same table multiple times.

So you'd need:

Main table
mainid
pos1id
pos2id
pos3id
otherinfo

Peoples table
PeopleID
Other info

Query something like:

Select maintable.*, qrypos1.*, qrypos2.*
from (maintable left join peopletable as qrypos1 on maintable.pos1id=qrypos1.peopleid) 
left join peopletable as qrypos2 on maintable.pos2id=qrypos2.peopleid

Vince

 

[Len Boorman]

Basically a Many to Many relationship should always be decomposed into two 1 to many relationships. If you cannot do this then I would suggest you review carefully the normalisation of the tables.

Len B

 

[Milothicus]

Ok, i'l be leaving it as is, then. my tables are fully normalized, and i'm trying to continue that with these relationships. i could have had 3 separate lookup tables all containing the same list of people, but that's triplicating information, so i want just the one table that all three positions (foreign keys) in my main table refer to.

a related question, then...

in my back end, i set the relationships as described (with three instances of the same table (people, people_1, people_2) and it works great. but... when i start a new query in the front end, i can't seem to use the 2nd and 3rd relationships i created in the back end.

just for simplicity and later adjustment, i'd like to not have any table-to-table relationships that reside only in the front-end, but 'people' only comes up once with all 3 relationships connecting to it. when i try to display the name of the person connected with pos1, the query doesn't know which position to connect the people table with (since all 3 go to the same table), so i have to manually add 2 instances of the table, and recreate the relationships in the front end.

 

[Len Boorman]

You will have to link the tables in the query where you have this sort of relationship because only you can decide which is the correct relationship. Bear in mind that People_1 and People_2 do not actually exist. They are there to illustrate the relationship and provide the referential integrity of the relationship.

I have an application that uses a recursive relationship

Basically a part can be made up of there parts and those parts can be made up of parts etc

So I have one parts table and one structure table. A part can be a part having parts or goes to make up another part...if you follow me

Drive this down seven levels and you have to define the relationship you want in the query cost all the other relations do not actually exist
HTH

Len B

 

[Milothicus]

so, in conclusion, this is not a many-to-many relationship. thanks for the reinforcement.

 

[Milothicus]

I knew something didn't seem quite right. i'll be making those changes now. thanks for the input. pat, as always, full of great info.

sorry, neileg, i guess i didn't understand your first post well enough to be convinced.

i'm still not sure how to make these changes...

the tables would be:

tblJnctn
JunctionID
JobFK (FK to tblJobs)
PersonFK (FK to tblPeople)
PositionFK (FK to tblPositions)

tblJobs
JobID (PK)
job info......

tblPeople
personID (PK)
person's info.....

tblPositions
positionID (PK)
position's info...

so each jobnumber can be associated with each of the 3 jobs in tblPositions and draw people's info from tblPeople.

look right?

 

[Milothicus]

I've set up the tables, and now have no idea how to use them....
i'd like it to look the same as before...

on one form (frmOverall) i have a combo to select the person for position1 and on another 2 combos to select the person for position2 and position3.

i have no idea what to bind my combos to. row sources all have ID and name from tblpeople (only name showing).

i can't search for many to many here, and the example i found in the sample database isn't anything like what i need...as far as i can tell.

i basically need each combo to make a new record in the junction table with all the relevant info, right? it doesn't seem to me like i should need to use code for this, though....i'm lost.....

 

[Milothicus]

found it...

here

 

[neileg]

Phew! Busy thread since I left it!

Pleased you got it sorted.

 

[Milothicus]

well, not entirely. still problems, but not sure where to start with them. once i know what the problem is, i'll be posting more.

 

[neileg]

At the moment, we don't really know what you are trying to do. All you've given is a snapshot of an area you perceive as a problem.

The danger is that you are being given 100% accurate advice, but that it's not relevant to the end results you want. Where there's a suspicion that your structure is wrong, you may be guided in the wrong direction because we make assumptions about what you want.

In project management terms assumption = risk!

 

[Milothicus]

I know. this project is a lot bigger than what you've seen. it's slowly expanding, and already in use, which makes structure changes more difficult. fortunately it's only being used by one person, and he knows i'm making changes, so it could be a lot worse.

i'm just drowning in some updates now........ i'll get through it.

 

[Len Boorman]

It's no consolation but we all have big problems with legacy systems cos mostly they were set up by people who had no knowledge of some of the principles involved with relational databases.

But you cannot always throw away what's there and start again (much as we would like to)

Len

 

[Milothicus]

well, i started this one myself about 3 months ago... when i knew nothing of access. most things i've been able to update to hopefully be normalized and documented enough to be understandable.

the problem is less with me, and more with the fact that the database started as a small enquiry tracking system, and every time i think i'm close to being finished, it seems there's new features needed that involve pretty fundamental shifts in the system...

it's a great learning experience

i'm building lots of character..and knowledge of access.

 

[Len Boorman]

Its a tough learning curve but as you say character building

I have only been doing them for about 5-6 years and I learn something new on every one

That's what makes them so interesting and stimulating.

So far I have never had a written spec of what is wanted and absolutely never has it stayed within the initial verbal outline. It has always expanded well beyond the original concept.

Normalisation is the key here because a fully normalised application is expandable much more easily.

Basically there is an architecture that says you should be able to expand the basic structure without affecting any of the interface.

Needs explaining a bit but a new (not a change to existing) function should be able to be added without changing anything of the interface. So yes add field to tables or new tables and make new table joins but in doing this you do not screw up anything you have already done.

Okay changes/additions to existing functions change the interface

Its a life full of frustrations and really interesting

But then it takes all sorts and I am one of those

Len

 

[Milothicus]

Well, as you can see from this table, i thought i was normalizing, but apparently wasn't. this actually happened in another aspect of my database, and so trying to correct all my forms and queries with these 2 changes is proving to be a nightmare. hopefully it'll be simpler soon.

still learning...