Hi all,
i’m new in this forum and not particularly skilled with Access (and english ).
So i apologize while i try to explain my problem.
I have to develop an application in Access 2010 to store, among other things, the meeting of a committee (board): date of meeting and commitee composition.
Committee is composed by four member , with one of them as president.
Commitee members belong to four associations and only those belonging to association A1 can play the role of president during a meeting.
One of them is elected President,but when he is not present, one of the other members of the same association acts as Substitute President (I don’t know the english for this). This information has to be reported somehow in the meeting report that I produce with Mail Merge.
My solution involves three tables :
MEMBER (MemberID, Name, AssociationFlag)
MEETING (MeetingID, Date)
ATTENDANCE (MemberID, MeetingID)
The AssociationFlag is the discriminator field used to represent the disjoint subtype for members (A1P : President member belonging association 1, A1 member of the association 1(act as substitute), A2 member of the association 2, and so on)
A typical table instance could be:
MEMBER
MemberID-Name-AssociationFlag
1-AA-A1 <- in this case the member is a substitute of the president
2-BB-A2
3-CC-A3
4-DD-A4
5-EE-A1P <- This is the member elected as President
ATTENDANCE
MemberID-MeetingID
1-1
2-1
3-1
4-1
MEETING
MeetingID-Date
1-17/05/2016
I need a query that,for each meeting, returns a row with all members info partecipating that meeting:
MeetingID-MeetingDate-A1Id-A1Name-A2Id-A2Name-A3Id-A3Name-A4Id-A4Name
1-17/05/2016-1-AA-2-BB-3-CC-4-DD
How can I achieve this?
Someone suggest me to modify the Meeting table to have four foreignkey A1MemberID, A2MemberId, A3MemberId, A4MemberId. I don’t like it. What do you think? Am I wrong ?
I hope I was clear. Thank you.