Deleting in a many to many relationship

[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

 

[Mike Labosh]

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)

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

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?"

 

[Jan Kronsell]

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