Many to Many Relationship

[Jennifer]

Good Day!

I have a main table named Nurse. I have a junction table named LUSpecialty
which contains the NurseID and the SPecialtyID, and I have a Specialty table.
A nurse can have many specialties. Here is my question: I need to create a
single field in the Nurse table that contains all of the specialties that the
nurse has. An update query will not work. Does anyone have any suggestions
on how I should approach this problem?

 

[golfinray]

I might approach it as a one-to-many. One nurse, many specialties. Then a
form to display the nurse and that nurse's specialties. You could have a
combo to look up nurses and display the specialties in the other fields.

 

[Michel Walsh]

That is what someone can call a LIST aggregate.

Here a solution implying a temporary table.


First, we make the temp table, two fields: the key, here, the nurse id, and
the second field which will hold the concatenation:

SELECT INTO temp SELECT DISTINCT nurseID, iif(false, " ", null) AS concat
FROM Nurses


The second step is to concatenate all the specialities, by nurseID:

--- untested ---
UPDATE (temp INNER JOIN luspeciality ON temp.nurseID = luspeciality.nurseID)
INNER JOIN specialities ON luspeciality.specialityID =
specialities.specialityID
SET concat = (concat + ", ") & specialities.specialityName
--- untested ---
(basically, a join between the temp table and the name of the specialities,
through the junction table, and you update the concatenation field, for the
matching nurseID, one at a time)


The temp table now hold the desired result. Note that if the concatenation
exceed 255 characters, you get a problem, since that is the maximum that can
hold a character field.



Vanderghast, Access MVP

 

[Jennifer]

Michel - that might just work! I see where you are going with it. I'll give
it a try tomorrow morning when I get back in to the office. I was actually
thinking about creating some custom programming that would do the same thing.
I just couldn't figure out how to do it in Access. I've never concatenated
in Access using a temp table so this will be interesting.

I'll let you know how it goes. Thanks for your input.

Jennifer

 

[Bob Barrows]

SELECT INTO temp SELECT DISTINCT nurseID, iif(false, " ", null) AS
concat FROM Nurses

Err, I believe you meant "INSERT INTO temp" ...

 

[Michel Walsh]

Nope,... Well, could, but the goal is to really make the temp table, and to
fill the second field with nulls and having a varchar data type; while the
first field would be the possible values for the 'groups'. SO, indeed, you
could use an INSERT INTO too, if such table already existed (and empty of
records).

Note that the second query won't work as intended in MS SQL Server, though.
It is just for Jet. The two engines do not process the UPDATE the same way:


UPDATE table SET a=b, b=a



in Jet, will end up with data identical in the two columns, while in MS SQL
Server, you end up with exchanging the data between two columns. But the
work done by MS SQL Server won't end up with a LIST aggregate, for the
supplied UPDATE.



Vanderghast, Access MVP

 

[Bob Barrows]

Nope,... Well, could, but the goal is to really make the temp table,

My point is that this is not valid syntax:

SELECT INTO temp SELECT DISTINCT

Your OP advised the user to first create the table and then run the
suggested query, so I assumed you typed "SELECT" instead of "INSERT"
inadvertantly.

I believe the correct syntax is to create and insert simultaneously is:

SELECT DISTINCT nurseID, iif(false, " ", null) AS concat INTO temp
FROM Nurses

 

[Michel Walsh]

Oh, yes, I see the point... indeed, you are right.


Vanderghast, Access MVP