Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    15

    Talking Unanswered: Count Unique Values From Text Field

    Hello All,

    I have a newbie type question..

    I have a simple table TableA that is comprised of four (4) fields:
    StudentName - Text
    Course - Text
    Grade - Text Values(A,B,C,D,F)
    Date - Date

    What I want to do from this table is to create a report that tells me how many A's, B's, C's, D's, F's each student has recieved. I figure I'll have to create a query to base the report against.

    What I'd like the report to look like is something simple like:

    ------------------------------------------------
    StudentName | A's | B's | C's | D's | F's |
    ------------------------------------------------
    Mary A | 1 | 2 | 0 | 0 | 0 |
    ------------------------------------------------

    In TableA, Student Mary A would have three (3) record rows with differing Course, Grade, and Date values; Two of the record rows would contain a grade of B and one record would contain a grade of B.

    I have been able to do this on a sample table when the data field I was attempting was a number field, but I am having all kinds of problems since the Grade field is a TEXT field.

    Any help is appreciated!

  2. #2
    Join Date
    Dec 2005
    Posts
    15

    Thanks..

    I think I found my answer, I modified a Cross Tab Query to get the the results I wanted, and then used a Report to exclude the fields I didn't want.

    Not sure if this was the most efficient way to accomplish this, but it worked. If anyone has another method or a better SQL statement, I'd be happy to use/learn from it.

    Thanks..

  3. #3
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Another way that I have had to do this when I need to filter(add criteria) to a crosstab type of result is to specify each column in a normal select query.

    So you would have your first column of Student name.

    Second column would be, A: IIf([Grade]="A",[Grade],Null)
    Third column would be, B: IIf([Grade]="B",[Grade],Null)
    Fourth column would be, C: IIf(Grade]="C",[Grade],Null)
    Same for D and F
    Then you can have columns for the course and date

    This would allow you to have a criteria form where you could use any of the fields above to drive your report. This way your report does not need to be modified when you change criteria.

Posting Permissions

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