Multi-value field count

[whitneynf08]

Thank your for taking the time to help me figure out my delima.

I am trying to create a query that will count the number of records entered into an fields.
The table name is: Awards
The field name is: Third Quarter
The items I would like to count from the combo box are: Student Advisor Relations, Student Centric Focus, Student Opportunity Discovery, Student Preparedness, Student Progression

I have tried the following with no luck:

SELECT Sum(IIF([Third Quarter] = "Student Advisor Relations", 1, 0)) AS "Student Advisor Relations:", Sum(IIF([Third Quarter] = "Student Centric Focus", 1, 0)) AS "Student Centric Focus:", Sum(IIF([Third Quarter] = "Student Opportunity Discovery", 1, 0)) AS "Student Opportunity Discovery:", Sum(IIF([Third Quarter] = "Student Preparedness", 1, 0)) AS "Student Preparedness:", Sum(IIF([Third Quarter] = "Student Progression", 1, 0)) AS "Student Progression:";

SELECT [Third Quarter], Count(*) As CountOfAwards
FROM Awards
GROUP BY [Third Quarter]

and

SELECT [Awards].Third Quarter, Count(*) AS [Count]
FROM [Awards]
GROUP BY [Third Quarter].Third Quarter;

[JoeM]

Your second option should work.
Are you getting errors or incorrect results (if errors, what does the error message say)?
Can you post a small sampling of what your data looks like?

[whitneynf08]

The second option gives me a an error saying that it doesnt like the GROUP BY clause with the Third Quarter field.

When the user selects the Awards they wish the employee to have, they can select multipule awards (there are a total of 5 to choose from).

One record may have: Student Advisor Relations, Student Centric Focus, Student Preparedness, Student Progression
Another record my have: Student Advisor Relations, Student Progression

I would like to create a query that will give me a total.
From this I would have:

Student Advisor Relations:2
Student Centric Focus:1
Student Preparedness:1
Student Progression:2

This selection is done quarterly, but right now I am focused on the Third Quarter.

I hope this helps.

[JoeM]

I totally missed you are using Multi-Valued Fields (I know, I know it is in the title). I am a bit old school, and like many of the old school programmers, avoid using them altogether.

Here is a write-up I found, Of particular interest to the problem at hand may be the section on using Aggregate Functions with Multi-Valued fields:
http://office.microsoft.com/en-gb/ac...49297.aspx#BM6