Deleting in a many to many relationship

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

Jan Kronsell

Hi NG

Im using Access 2002

I have a many to many relationship between tblMembers and tblTeams

Table tblMember contains Membernumber, Membername and so on (Membernumber
is primary key)
Table tblTeam contains Teamnumber, Teamname and so on (Teamnumber is
primary key)
Table tblMembership contains Membernumber, Teamnumber, date of membership
and so on (membernumber+teamnumber is primary key)

I want to delete all members from tblMembers, that are not participating on
a team (old members).

I have tried with tinhs like

DELETE FROM tblMembers WHERE tblMembers.Membership <>
tblMembership.Membernumber


and a lot of stuff like that.

How can I do what I want, if at all possible.

Jan
 
I have a many to many relationship between tblMembers and tblTeams
Table tblMember contains Membernumber, Membername and so on (Membernumber
is primary key)
Table tblTeam contains Teamnumber, Teamname and so on (Teamnumber is
primary key)
Table tblMembership contains Membernumber, Teamnumber, date of membership
and so on (membernumber+teamnumber is primary key)
Click to expand...
DELETE FROM tblMembers WHERE tblMembers.Membership <>
tblMembership.Membernumber
Click to expand...

This should do the delete the right way, but then you will strand all those
member numbers in tblMembership. So you should consider setting the option
on the relationship to "Cascade Deletes"

DELETE
FROM tblMembers
WHERE Membernumber NOT IN
(SELECT DISTINCT Membernumber
FROM tblMembership)

--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"
 
Thank you. That did the trick :-)

There will be no stranded member numbers in tblMembership, as only those who
are not deleted from tblMembers are in tblMembership in the first place, and
they shall remain there. So no cascading is necessary.

Jan
 
Back
Top