Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab results (2002 SP3)

    I have a table with two fields, StudentNum and Level. Each student # may have more than one level. What I need is to have something like a crosstab query that would give me one row for each student and each level for that student in a column. I have tried every way I can think of to give me those results but can not get it it to work. Examples of levels would be CASE, COR, DP, SC, etc. Can anyone point me in the right direction.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Crosstab results (2002 SP3)

    Try this:
    - Create a query in design view.
    - Add the relevant table.
    - Select Query | Crosstab Query.
    - Add the StudentNum field, and set the Crosstab option to Row Header.
    - Add the Level field, and set the Crosstab option to Column Header.
    - Add the Level field again, set the Total option to First and the Crosstab option to Value.
    Does that do what you want?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab results (2002 SP3)

    What that did was create 7 columns, one for each level with the name of the level in the column. In a report, how would I show just the one or two levels for a student without all the blank levels that were not applicable?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Crosstab results (2002 SP3)

    That's not the way a crosstab query works. Here is an alternative:

    The attachment to <post#=301,393>post 301,393</post#> contains the code for a function named Concat that you can use to concatenate values into one long string. Copy the function into a standard module. You can then use it in a query:
    - Create a query in design view.
    - Add the relevant table.
    - Select View | Totals or click the Totals button on the toolbar.
    - Add the StudentNum field to the query grid. The Total option will be set to Group By, that is fine.
    - Enter the following expression in the second column, substituting the name of the table:
    <code>
    Levels: Concat("NameOfTable", "Level", "StudentNum = " & [StudentNum])
    </code>
    - Set the Total option for this column to Expression.

    Notes:
    1) You need to have a reference to the Microsoft DAO 3.6 Object Library in Tools | References... in the Visual Basic Editor.
    2) The above expression assumes that StudentNum is a number field. If it is a text field, use
    <code>
    Levels: Concat("NameOfTable", "Level", "StudentNum = " & Chr(34) & [StudentNum] & Chr(34))</code>

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab results (2002 SP3)

    Thanks for your help. I think I remember reading about Concat in previous posts but when I did a search, I didn't search for the right words.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

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