Query not returning all the records I need.

[upsman via AccessMonster.com]

I have 3 tables - Students, which contains all students; Attendance, which
contains dates a particular student was absent; and AttendanceCtrl which
contains Quarter beginning/ending dates. I need to be able to calculate the
number of days a student was present, absent, tardy in a quarter. The query
needs to extract all students regardless of whether or not they missed any
days. I tried using a Left join Students to Attendance and then joining
AttendanceCtrl but Access said it couldn't do it because of "ambiguous outer
joins". It said to create the first join then use that query in the second
join. I tried that. I created a query with a Left join joining Students to
Attendance. That gives me what I wanted, all students from Students and also
those students with matching records in Attendance.

The 2nd query is giving me problems. I tried joining the first query
(qryStudents) to AttendanceCtrl and calculating my totals. Problem is, the
query only gives me students that have records in the Attendance file; it
doesn't give me all the students. If a student did not miss any days, I need
to show that he was present 90 days out of 90. Because there's no matching
record in the Attendance file, I get nothing for those students.

As I said the first query (qryStudents) seems to be working but I need to
take the records from that query, group them together by StudentID, and then
calculate my totals. How can I do this? Am I approaching this wrong
altogether?

Thanks for any help.

 

[Tom Ellison]

Dear ups:

Recommend you post the SQL code of all this so we can look for problems.