Results 1 to 4 of 4
  1. #1
    liuc is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2016
    Posts
    2

    Committee membership + rows to column transposition

    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.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    This is basic query design, bring all 3 tables into a query
    join
    member.memberID to attendance.membered
    and
    attendance.meetingid to meeting.meetingid
    and bring in all fields you need.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "Name" and "Date" are reserved words and shouldn't be used as object names.

    Consider:
    MEMBER (MemberID, MemberName, AssociationFlag)
    MEETING (MeetingID, MeetingDate)
    ATTENDANCE (MemberID, MeetingID, President)
    -----------------------------------------

    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-A1 <- This is the member elected as President
    --------------

    ATTENDANCE
    MemberID-MeetingID-President
    1-1-S ' "P" for President / "S" for Substitute President
    2-1
    3-1
    4-1
    --------------

    MEETING
    MeetingID-Date
    1-17/05/2016
    --------------------------------


    To transpose records to columns, you will have to concatenate the member attendance records for a meeting using a function.
    See Allen Browne's function at http://www.allenbrowne.com/func-concat.html

  4. #4
    liuc is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2016
    Posts
    2
    Thank you so much.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-18-2014, 08:22 AM
  2. Table transposition - how to
    By Adams in forum Access
    Replies: 3
    Last Post: 10-28-2013, 10:27 AM
  3. Rows into Column in a Report!
    By cap.zadi in forum Reports
    Replies: 5
    Last Post: 01-20-2012, 07:00 AM
  4. split column into rows.
    By rsampathy in forum Access
    Replies: 1
    Last Post: 03-03-2011, 12:15 PM
  5. Sum of X's in column not rows, possible?
    By CoachBarker in forum Queries
    Replies: 7
    Last Post: 02-09-2011, 12:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums