I am currently creating a database for my church office which consists of 1 Parish and 3 churches which come under that parish. I need to make a form to be able to view who currently holds the important positions within the Parish and 3 churches (eg. President, Secretary, Treasurer). Each member belongs to only 1 church so can only hold a position in one of the 3 churches, but they are all members of the parish so can not only hold a position within their church but also within the parish. I would also like to be able to view the members contact details for ease of the user and passing the information onto the district office.

The current positions that can be held within the 4 offices is: President, Vice-President, Secretary, Vice-Secretary, Treasurer, Vice-Treasurer. There may be more positions to be added in the future, but this will suffice for now.
The positions change on an annual basis. (A member may only hold the position for a year, but may also be re-elected).
I would also like to have the option of viewing positions held from previous years.


I have tried several times to nut this out, but with little success and also coming to what seems to be a brick wall. Any held would be greatly appreciated.

Current Tables:

RecordT:
RecordID
Surname
FirstName
PreferedName
Address
Phone
Mobile
Email

OfficeT:
OfficeID
Office (Parish, Rainbow, Pella, Hopetoun)

HoldOfficeT:
HoldOfficeID
OfficeID
RecordID
Position
OfficeYear

Also had/have a table for postion, but are not sure if this is necessary or complicating my problem.

I would like a form to look something like the following to input the information: (basic example only)

Year of Office: 2009 (Enter the current year)
Office: Parish (To be a combo box to select which office)

Position  Name   Phone Mobile Email
President J.Smith 12345 67890 [email protected]
Vice-Pres
Secretary
Vice-Sec
Treasurer
Vice-Treas

I don't want to have to continually add the position in. And this is what seems to be stumping me. I can at this stage select the person and have their information automatically populate the rest of the table.