Count Query

[ryadav]

Hi

I am running a count query at the moment which shows the number of classes booked for each faculty. The query is as follows:

SELECT Audited_Week02.Faculty, Count(Audited_Week02.Faculty) AS CountOfFaculty
FROM Audited_Week02
GROUP BY Audited_Week02.Faculty;

So when the query is run the following data view appears:

Faculty CountOfFaculty
AAD 3
BAL 2
CSE 51
HLS 78
N&M 42
TO 22

Which is fine, however there is another query as well which shows all the classes that didn't take place for these faculties, therefore I want to include this as well. So I have tried:

SELECT Audited_Week03.Faculty, Count(Audited_Week03.Faculty) AS CountOfFaculty, Summary_W02.Faculty, Count(Summary_W02.Faculty) AS CountOfFaculty1
FROM Audited_Week03, Summary_W02
GROUP BY Audited_Week03.Faculty, Summary_W02.Faculty;

But when I run the query I get the following data view:

Audited_Week03.Faculty CountOfFaculty Summary_W02.Faculty CountOfFaculty1
AAD 26 AAD 26
AAD 104 CSE 104
AAD 26 HLS 26
AAD 104 N&M 104
AAD 130 TO 130
BAL 118 AAD 118
BAL 472 CSE 472
BAL 118 HLS 118
BAL 472 N&M 472
BAL 590 TO 590
CSE 58 AAD 58
CSE 232 CSE 232
CSE 58 HLS 58
CSE 232 N&M 232
CSE 290 TO 290
HLS 8 AAD 8
HLS 32 CSE 32
HLS 8 HLS 8
HLS 32 N&M 32
HLS 40 TO 40
TO 4 AAD 4
TO 16 CSE 16
TO 4 HLS 4
TO 16 N&M 16
TO 20 TO 20

When it should be:
Faculty CountOfFaculty Faculty CountOfFaculty
AAD 3 AAD 2
BAL 2 BAL 0
CSE 51 CSE 8
HLS 78 HLS 2
N&M 42 N&M 8
TO 22 TO 10


The other possibility is to create a separate query for classes that didn't take place then create another query which joins these 2 up. As you can see it will start to get confusing so it would easier if classes that are booked and classes that didn't take place were in one query.

Is this possible, because there are altogether 25 weeks of data that need to be counted?

thanks

 

[Brianwarnock]

What happens if you include a join on Faculty?

Brian

 

[ryadav]

I get the following data view:
Audited_Week02.Faculty CountOfFaculty Noclass_CalcW02.Faculty CountOfFaculty1
AAD 3 AAD 3
CSE 51 CSE 51
HLS 78 HLS 78
N&M 42 N&M 42
TO 22 TO 22

The query is:
SELECT Audited_Week02.Faculty, Count(Audited_Week02.Faculty) AS CountOfFaculty, Noclass_CalcW02.Faculty, Count(Noclass_CalcW02.Faculty) AS CountOfFaculty1
FROM Audited_Week02 INNER JOIN Noclass_CalcW02 ON Audited_Week02.Faculty = Noclass_CalcW02.Faculty
GROUP BY Audited_Week02.Faculty, Noclass_CalcW02.Faculty;

By the way I renamed the tables, so they would make more sense.

 

[ryadav]

Sorry ignore my last reply, they are the wrong tables. In regards to your earlier comment, I have included a join on Faculty but it just freezes, so i have to click on task manager to come out of it altogether.

 

[Brianwarnock]

Hmm, sorry but I have know idea why that should happen.

Brian