Relationships/Data format (Access 2003)

[bonalymac]

I am a keen beginner. I can usually manage to make something work, but being completely self-taught, it rarely the most efficient.

I am struggling with one problem. I have one database where i keep a list of people, all of whom appear as either the 1st, 2nd, 3rd or 4th member of various committees (number field linked to autonumber ID in people table). I need to keep a note of which position. I started with the idea of having a single list of people, and then multiple relationships. First to committee position 1, then to posn 2 etc. No matter what I tried, I could not make this work..

As a solution, I copied the people table to create 4 identical copies of the table. I could then create relationships between each committee posn and the list of members. As there were not too many people, this did not create too much of an overhead. I now have similar prob, with a far larger list of names, and I'm sure it can be done better.

Any suggestions would be welcome.

[WendellB]

This is most often handled with a linking table, so that you have a simple two column (field) table with a pointer to the people table and a pointer to the committee table. If you want to track the position on the committee, you could create a third column which indicated the position as a number. If you want to force the positions on a specific committee to only have one member, you could put a Unique values only on the committee and position number. Hope I understood your dilema, and this helps.

[bonalymac]

Thanks

It sounds as if you've understood it.

I'll try it tomorrow morning