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.

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 September 4, 200520 yr by Guest
Later slight improvement