Count query

[Beth]

I am struggling with how to do a query that will show the information I
need.
First is a table (tblAttendance) with a StudentId and dateofAttendance
Every time a student attends a class, they are entered as a record in the
table.

I want to have a query that shows all my students (even if they have no
attendance) followed by the number of days they attended class. Sometimes,
I am going to want to filter the query to those with less than a certain
number of days of attendance and I may also want to limit it to show
attendance between 2 dates.

I have tried a count query, but it doesn't show students with no attendance.

Thanks in advance for the help.

Beth

 

[Jeff Boyce]

Beth

Use two tables in your query.

First, include the tblStudent, so you can get all students.

Then add the attendance table, joined on StudentID. Modify the properties
of the join so that you will see ALL students and ANY Attendance. I assume
you have an AttendanceID -- if not, use the DateAttended. And you might be
able to use the Nz() function to convert no-attendance to a 0.

This query then becomes the source for another query that does the counting
(Totals query, group by studentID, count AttendanceID or, if you don't have
that, count the DateAttended).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Beth]

Jeff,
Thanks. That works. Now, to make life complicated, is there a way to do
this in a single query? I am looking to not have stand alone queries in the
query section of my database, but just set the datasource in the form or
report with the same structure as the query. As far as I can tell, that
means I have to do it in a single query statement. If that's not possible,
I'll just store the first query and build the report datasource on that
query.

Beth

 

[Jeff Boyce]

Beth

If you point your form to the second query, that query "runs" the first one.

Regards

Jeff Boyce
Microsoft Office/Access MVP