We help IT Professionals succeed at work.

Query table for how many of class types and how many students completed during year.

John Kincaid
John Kincaid asked
on
Access 365 query question.

Table fields are:
   [Date of class]
   [Class Type]
   number of [Students Completed].

I need a query that shows,
   Year: DatePart("yyyy",[Date of Class])
   Count of [Class Type]
   [Class Type]
   Sum of [Students Completed]
Comment
Watch Question

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Try something like this perhaps:

SELECT Year([Date Of Class]) AS TheYear, Count(YourTable.Class Type) AS CountOfClassType, Sum(YourTable.Students Completed) AS SumOfStudentsCompleted
FROM YourTable
GROUP BY Year([Date Of Class]);

Open in new window

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
...or like this?
SELECT Year([Date Of Class]) AS TheYear, Count(YourTable.[Class Type]) AS [CountOfClass Type], YourTable.[Class Type], Sum(YourTable.[Students Completed]) AS SumOfStudentsCompleted
FROM YourTable
GROUP BY Year([Date Of Class]), YourTable.[Class Type];

Open in new window



Sounds like a "summary" query is what you are after here.

You can probbaly figure out how to manipulate the fields in the QBE grid to get what you want.

Let us know
Distinguished Expert 2017
Commented:
I need a query that shows,
   Year: DatePart("yyyy",[Date of Class])
   Count of [Class Type]
   [Class Type]
   Sum of [Students Completed]
1. As long as you include detail in the query i.e. [Class Type], you can't produce a count of the types of classes. The count will always be 1.
2. Even removing [Class Type] will not allow you to produce a count of [Class Type] AND a count of [Students Completed] in the same query

you can produce a query showing Year, CountOfClassType, CountOfStudentsCompleted by creating a query to count class types and a second to count students and then a third query to join the two queries.  The two queries will join on the Year field.

Author

Commented:
that’s what I was thinking I would have to do. thanks Pat

Author

Commented:
thanks everyone
Distinguished Expert 2017

Commented:
You're welcome:)