Jump to content

Club lists from many to many relationship - How?


This topic is 6671 days old. Please don't post here. Open a new topic instead.

Recommended Posts

This looks so simple but I cannot make it work'

I have three tables

Members: with 2 fields MemberID, Name

Clubs: with 2 fields ClubID, Name

Membership: with 4 fields ID, MemberID, ClubID, Position

(There are other details but they are irrelevant to this question. All IDs are auto-enter serial numbers)

There is a many-to-many between clubs and members and I am using the membership table as a "sort of join". Position refers to a particular members position within the club heirarchy, Chairman, Treasurer,...Committee member and a member can hold more than one position so ClubID & Member ID do not uniquely determine a record in the membership table (this may be the root cause of the problems).

What I want to achieve is this. Looking at a layout taking its records from a members table TO I would like to have in the same layout a portal containing all the other members of, and their positions within, the clubs to which the member belongs. If this portal could be filtered to show one club at a time even better.

My first question: am I recording the position in the right place? The problems I am having seem to be with regard to getting the position, individual name and club name all showing correctly in the portal. I can get one or two out or three but so far failed to get all three.

Second question. If I am recording position in the right place what kind of relationship diagram do I need here and If I am not recording position in the right place where should it go? and what is the relationship diagram?

I do not want to change to a layout based on a club table TO if it is avoidable.

All help gratefully received.

Link to comment
Share on other sites

OK I have sorted it out. Writing it down helps. I have taken out the Positions into a separate table and then used the membership tables as a "star" join with the other three tables. Made a second copy of the 4-table join and then joined the two copies of the membership table by Club ID and by a global number field to Club ID to enable the filtering by club. A portal into the second membership tables with the global field as a filter did the job.

Edited by Guest
Later slight improvement
Link to comment
Share on other sites

This topic is 6671 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.