Using the count function in access queries (1 Viewer)

[ryadav]

Hi

I have a table which has information the count of students in classrooms around the university and I need to summarize the table by Faculty. Therefore, all I want to do is a count of students for each faculty i.e. Art and Design, Business and Law etc.

The query i put together is: SELECT Count([tbl_Audited Classroom for Week 02].Faculty) AS CountOfFaculty
FROM [tbl_Audited Classroom for Week 02]
HAVING (((Count([tbl_Audited Classroom for Week 02].Faculty))="BAL"));

So when I run the query I get the error message 'Data Type mismatch in criteria expression'. The Faculty field is a text field, so I don't know if that would make a difference.

Can you please help?
thanks

 

[Dennisk]

HAVING (((Count([tbl_Audited Classroom for Week 02].Faculty))="BAL"));

you are testing a count ie a number against a text string

 

[ryadav]

So what do I pick from the Totals drop down list?

 

[dcobau]

g'day,

I would group on the faculty field and count the student names. The following SQL count the number of post codes for each state of Australia

SELECT tblPostCodes.State, Count(tblPostCodes.Pcode) AS CountOfPcode
FROM tblPostCodes
GROUP BY tblPostCodes.State;

Dave

 

[ryadav]

A million thanks to you, its worked!!

thanks again

 

[ryadav]

Just one more thing

The query so far shows all classes booked in for the faculties but I need to include another table in this query, which is comparing the number of faculties that didn't have classes taking place. So I have tried:

SELECT tbl_Audited_Week02.Faculty, Count(tbl_Audited_Week02.Faculty) AS CountOfFaculty, Summary_Week02.Faculty, Count(Summary_Week02.Faculty) AS CountOfFaculty1
FROM tbl_Audited_Week02 INNER JOIN Summary_Week02 ON tbl_Audited_Week02.Room = Summary_Week02.Room
GROUP BY tbl_Audited_Week02.Faculty, Summary_Week02.Faculty;

But the query doesn't run properly. Please help.