Results 1 to 3 of 3
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Columns to Rows (2003)

    Every time I think I finally know something about Access, along comes another simple-looking thing I can't handle.

    tblReunionAttendees has two columns: Handle and ReunionAttended. Handle is a unique text identifier for each member of our group. ReunionAttended is a four-character "year" with five possible values: 199x, 2004, 2005, 2006, and 2007. There will be more values added as reunions are held in future years.

    There is one entry in the table for each attendee at each reunion. That is, for the 50 people who attended the 199x reunion, there are 50 records in the table, with two fields each: Handle and ReunionAttended. The ReunionAttended field will contain "199x" for each of these 50 people. There are similar numbers of records for other reunions.

    If a member never attended a reunion, his Handle is not in the table. If he attended one or more reunions, there is a record for each one, each record consisting of his Handle and the ReunionAttended "year."

    I want to do something that is probably simple, but beyond my ken right now. I want to end up with a table, query, or Excel worksheet with a row for each unique Handle in tblReunionsAttended, and with columns for each of the five reunion "years." If the member attended a reunion, there will be an entry next to his Handle, under the appropriate year. If he attended several, there will be several entries next to his handle. After the next reunion, I'll want to add a column for 2008 and run the query again.

    How can I get what I want?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Columns to Rows (2003)

    If you are looking for output something like this

    <table border 1><td>Handle</td><td>199X</td><td>2004</td><td>2005</td><td>2006</td><td>2007</td><td>Lou</td><td>X</td><td>X</td><td> </td><td> </td><td>X</td><td>mbarron</td><td> </td><td>X</td><td>X</td><td>X</td><td> </td><td>Sam</td><td> </td><td> </td><td> </td><td>X</td><td>X</td></table>

    you can use a cross tab query based on your table.
    Attached Files Attached Files

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Columns to Rows (2003)

    That's EXACTLY what I'm looking for. I've heard of crosstab queries, but haven't ever had the occasion to do one or look into them further. I'll do it now.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •