One small problem with my query

[Chris Lynch]

Hi all

I have the following query that will show the date, studentname, number of classes marked absent for that date.

i also want to show the total number of classes in the day, this is needed to make a quick comparsions to the total number of classes missed per day.

This is the code

SELECT Attendance.Date, Count(Attendance.Attended) AS CountOfAttended, Student.StudentName, Count(Attendance.ClassID) AS CountOfClassID
FROM Student INNER JOIN (Groups INNER JOIN (Classes INNER JOIN Attendance ON Classes.ClassID = Attendance.ClassID) ON Groups.GroupID = Attendance.GroupID) ON Student.StudentID = Attendance.StudentID
WHERE (((Attendance.Attended)='  Absent'))
GROUP BY Attendance.Date, Student.StudentName, Student.StudentID
HAVING (((Student.StudentID)=[Forms]![Frm]![cboStudent]));

This shows the date, the number of classes the student was absent for, the student name, the total number of class in that day.
But my problem is that it only shows the total number of classes marked absent again..... IS there a way to exclude the where clause from the Count(Attendance.ClassID) AS CountOfClassID part of the code...

Help is is welcome
Thanks
Chris Lynch

 

[DanG]

I'll take a stab at this. I'm a little new at this helping thing though...
Make a totals query to count the total classes and them bring that into you current query.

 

[Chris Lynch]

I don't follow?

Chris Lynch

 

[Mile-O]

Have you done any search on Totals Query?

 

[Chris Lynch]

I found something onthe web that showed two queries been used for something similiar to what i need , but when i go to try this it won't let me do it.

Select distinct studentid, Convert(varchar(30),classdate,101) as classdate
Into #notabsent
From classes
Where attend <> 'absent' and studentid = @studentid

select studentid, Count(*) as NumberOfDaysAbsent

From classes
Where Convert(varchar(30),classdate,101) not in (Select classdate from #notabsent)
Group by studentid
HAving studentid = @studentid

This is what i found and i tried changing it to suite my db but it would't allow it

 

[Brianwarnock]

DanG was suggesting a second query counting classe by date and then bringing both queries into a third joined by date, atleast I think he was, wont that work?

Brian

 

[Chris Lynch]

HI, now i have created two different queries. One that shows the total number of classes for a student and one that shows the total classes marked absent for a student. now in the query that i open i have in one field

Name of query: Field i want from other query
TotalClass: ClassID
table data is taken from
Count

Is this the wrong way to call data from one query to another

Thanks
Chris Lynch

 

[Brianwarnock]

Sorry for the delay in responding our server at work went down!

No

In your 3rd query you select the 2 previous queries just as you would tables and join them in this case on a Date, ah! never use Date as the name of a field it is a reserved word in access a name of a function and chaos will at some time follow,then select from the two queries the fields required.

brian

PS you may like to follow this Link to Naming conventions discussion

 

[Chris Lynch]

Don't worry about it, i got in done yesterday anyway, i never notice that you could add a query to the query the same way you add tables

Thanks Anyway
Chris Lynch