Results 1 to 5 of 5
  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

    Simple Query (2003)

    I have a table consisting of two text fields: CustomerID and ReunionAttended. We've had four reunions, and each CustomerID has attended at least one of them. Many CustomerIDs have attended two or more reunions.

    The table includes all the CustomerIDs who attended the 2004 reunion in one field, and "2004" in the other, followed by all those CustomerIDs who attended the 2005 reunion and "2005," and so forth for 2006 and 2007. In other words, there is one record for each attendee at each reunion. If somebody has attended three reunions, their CustomerID appears three times in the table, each with a different ReunionAttended entry.

    I want to do a query with one record for each CustomerID that is present in the table. It will have five fields: Customer ID, 2004, 2005, 2006, 2007. In each of the latter fields will be something (a letter, TRUE, or whatever) to show that the corresponding CustomerID attended that reunion.

    How can I construct it?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Query (2003)

    Create a query in design view based on the table.
    Select Query | Crosstab Query.
    Add the CustomerID field, leave its Total option alone (the default Group By is OK), and set its Crosstab property to Row Heading.
    Add the ReunionAttended field, leave its Total option alone and set its Crosstab property to Column Heading.
    Create a calculated column

    V: Count(*)

    Set its Total option to Expression and its Crosstab property to Value.
    Switch to datasheet view to see the result.

  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: Simple Query (2003)

    Great! It does exactly what I need it to do.

    Now... I also have tblAllCustomers, which has fields for Name, Address, Phone Number, etc. for each CustomerID. How can I add some of these fields to my crosstab query? (I DO have some query skills, but they aren't deep, and I don't use them too often.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Query (2003)

    Create a new query in design view.
    Add tblAllCustomers and the crosstab query.
    Join them on the CustomerID field (i.e. drag from CustomerID in the table to CustomerID in the crosstab query)
    Add the fields that you need from either to the query grid.

  5. #5
    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: Simple Query (2003)

    All hail Mighty Hans!

    It works perfectly, and does exactly what I need.

    What a wonderful world we live in! And Woody's Lounge is one of the most wonderful parts of it.
    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
  •