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

John Kincaid
John Kincaid used Ask the Experts™
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017
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.
that’s what I was thinking I would have to do. thanks Pat
thanks everyone
Distinguished Expert 2017
You're welcome:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial