COUNT function help needed

[Lynn Cohen]

I am working on schedule data at a college using an Access 2010 database. I'm somewhat new to Access 2010, but have used Access 2003 for several years.

I have a table that holds records for each course that has run in a year. A course can be run several times in a year, and so most courses have more than one section number. For example, there may be ACC 101-01, ACC 101-02, and ACC 102-03, where ACC 101 and ACC 102 are course numbers and 01, 02, 03 are section numbers.

I want to count, by course number, the numbers of sections that actually ran, for a year. The results I want would look like:


ACC 101 2
ACC 102 1

I created Query 1, which returns each course with all its section numbers for the year. I deselected sections that were cancelled. Example: ACC 101-04 was cancelled, and so was deselected.

I copied Query 1 to create Query 2. In Query 2 I applied the COUNT function to the section numbers column. The numbers I am getting (as confirmed by a manual count) indicate the sections I deselected in Query 1 are no longer deselected. Example: I am getting a total of 3 for ACC 101.

Would appreciate any pointers. Thanks!

[June7]

How did you 'deselect' record? What is the filter criteria? Is there a 'Cancelled' field?

The course/section value is all in one field?

Show the attempted query SQL statement. Something like:

SELECT Left([Course],7) AS CourseID, Count(Right([Course],2)) AS Section WHERE Cancelled = False GROUP BY Left([Course,7);

[Lynn Cohen]

>>How did you 'deselect' record? What is the filter criteria? Is there a 'Cancelled' field?
Records for cancelled sections have a unique "Faculty ID" number, number 261. In Query 1, I set a parameter in the "Faculty ID" field that says not 261.

>>The course/section value is all in one field?
"Course" and "Section Number" are two separate fields.

>>Show the attempted query SQL statement.
I don't have an SQL statement. In Design View, I clicked on the "sum" sign in the Show/Hide tab which added a row called "Show" to the Design View. This puts a qualifier, "Where" in each column. In the column for "Section Number" I changed the qualifier "Where" to "Count."

[June7]

You said you built Query1 and Query2. If you did build queries then there is SQL statement. It can be seen in the SQL View of the query builder. Copy/paste in post so we can analyze.

What you describe is an aggregate (GROUP BY) Totals query.

Consider building a report using Grouping & Sorting with aggregate calcs in footer sections. This allows display of detail records as well as summary data.

[Lynn Cohen]

You said you built Query1 and Query2. If you did build queries then there is SQL statement. It can be seen in the SQL View of the query builder. Copy/paste in post so we can analyze.

What you describe is an aggregate (GROUP BY) Totals query.

Consider building a report using Grouping & Sorting with aggregate calcs in footer sections. This allows display of detail records as well as summary data.

******************************************
Thanks to everyone who replied.

I was able to set up a series of queries that performed the calculations I need.

• Query 1 identifies all sections of each course.
• Query 2 counts numbers of sections per course.
• Query 3 identifies all sections of each course that ran in the online format.
• Query 4 counts numbers of online sections per course.
• Query 5 returns: course number, # sections identified in Query 2, # sections identified in Query 4.

This format allows me to de-select certain sections we don't want to count, in Query 1 and Query 3.

Lynn