Count date only one time per occurance

[Guest]

I am trying to count a single instance of a date that meets a condition.
such as count where a clas was taught by a a specific individual on a
certain date.

data is coming from a sign in sheet that has the date and the teacher.

right now it is basically counting each student as an instance

Thank you

 

[John Spencer]

You have to get a unique incidence of the class, date, and instructor.

First query would be something like
SELECT DISTINCT Class, ClassDate, Instructor
FROM RosterTable

Now you can count on that query.
SELECT Instructor, Count(ClassDate) as TheCount
FROM TheFirstQuery


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thank you that is basically what I needed except that the teachers with null
entries stil count as 1
can I select distict with a condition that eliminates nulls?

 

[Michel Walsh]

SELECT teacher, COUNT(*)
FROM (SELECT DISTINCT teacher, dateOfClass FROM somewhere)
GROUP BY teacher



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

A COUNT(fieldName) does NOT count the NULL as one. If you get a count of
one for those, then these fields are probably strings of blanks rather than
null. You can still add a WHERE clause that explicitly exclude rows with
those values:


WHERE 0 <> len(Trim( "" & ClassDate ))


Vanderghast, Access MVP