title: How do I merge multiple records in Access 2007 into a single record?
body: <p>I've searched through numerous discussions and have been unable to find a solution to my particular problem.  I have an Access 2007 database with a Faculty table and Sessions table in a many-to-many relationship.  Faculty members can be in more than one session and can have different roles (presenter, moderator, facilitator, etc.) depending on which session you look at.  I am trying to create a query that lists a faculty member's roles in a single record across all the sessions they are in.  Currently, I get a single record for each session that a faculty member is in unless their roles are identical in another session.  I want to see their roles across the board independent of the sessions they are in.</p>
  
  <p>I'm part of the way there.  Below is the SQL code for the query I currently have.</p>
  
  <pre><code>SELECT DISTINCT 
    FacultySessionJoin.FacultyID, 
    Faculty.FirstName, 
    Faculty.LastName, 
    Faculty.Email, 
    FacultySessionJoin.Presenter, 
    FacultySessionJoin.Moderator, 
    FacultySessionJoin.Facilitator
  FROM 
    Faculty 
  LEFT JOIN 
    FacultySessionJoin ON Faculty.FacultyID = FacultySessionJoin.FacultyID
  ORDER BY FacultySessionJoin.FacultyID;
  </code></pre>
  
  <p>I'd post an image of the results, but the site won't allow me to.  Here's an example of my problem:</p>
  
  <pre><code>Faculty ID  FirstName  LastName  Presenter  Moderator  Facilitator
  3            Vanessa    Smith      Yes         
  3            Vanessa    Smith      Yes        Yes
  </code></pre>
  
  <p>How would I combine all such records in my query into a single line such as:</p>
  
  <pre><code>Faculty ID  FirstName  LastName  Presenter  Moderator  Facilitator
  3            Vanessa    Smith      Yes        Yes
  </code></pre>