Count How Many Students had more than one tutorial?

[kate10123]

Hi there,

I have what seems a tricky requirement. I have some basic totalling queries to tell me, how many tutorials have been recorded and even how many different students have had tutorials. But what I need to look at now is to get a total number of students that have had more than 1 tutorial.

All of the relevant fields for this query are in table tbl_tutorial. The unique ID in this table that I have been using to count tutorial records is TutorialNo, I have also used StudentID to count how many students.

Here is my attempt so far:

SELECT Count(tbl_Tutorial.TutorialNo) AS CountOfTutorialNo, Count(tbl_Tutorial.StudentID) AS CountOfStudentID
FROM tbl_Tutorial
HAVING (((Count(tbl_Tutorial.TutorialNo))>1));

But this seems to just count the total number of records.

I would appreciate a step by step instruction so I can learn from this.

Thanks for any replies in advance

 

[datAdrenaline]

A SQL statement like this should do the trick ...

SELECT Count(StudentID) As StudentsWithTwoOrMore
FROM (SELECT StudentID
      FROM tbl_Tutorials
      GROUP BY StudentID
      HAVING Count(TutorialNo) > 1) As vTbl

 

[LPurvis]

The aggregate Count function doesn't Count instances of distinct values in a given field. It counts the total of non-Null occurences.
If both fields are required then you might as well use the, technically more efficient, Count(*).

Of course - that wouldn't get you anywhere here - you're already finding that this is analogous to counting all records in both cases.
i.e. what you have is effectively.
SELECT Count(*) AS CountOfTutorialNo, Count(*) AS CountOfStudentID

You'll need to at least group by the students involved

SELECT StudentID, Count(tbl_*) AS TutorialsTaken
FROM tbl_Tutorial
GROUP BY StudentID
HAVING Count(*)>1

If you're wanting further data than that you might be using that as a subquery.
What kind of data are you wanting back exactly?

 

[datAdrenaline]

>> If you're wanting further data than that you might be using that as a subquery <<

... Hmmm ... I couldn't agree more ... .... ROFL!!! ... 3 minutes is a long time!! ... what took you so long!

{Note to poster: Leigh and I go way back ... so jovial ribbing between us is quite common!}

 

[MSAccessRookie]

Hi there,

I have what seems a tricky requirement. I have some basic totalling queries to tell me, how many tutorials have been recorded and even how many different students have had tutorials. But what I need to look at now is to get a total number of students that have had more than 1 tutorial.

All of the relevant fields for this query are in table tbl_tutorial. The unique ID in this table that I have been using to count tutorial records is TutorialNo, I have also used StudentID to count how many students.

Here is my attempt so far:

SELECT Count(tbl_Tutorial.TutorialNo) AS CountOfTutorialNo, Count(tbl_Tutorial.StudentID) AS CountOfStudentID
FROM tbl_Tutorial
HAVING (((Count(tbl_Tutorial.TutorialNo))>1));

But this seems to just count the total number of records.

I would appreciate a step by step instruction so I can learn from this.

Thanks for any replies in advance

Would something like the following work?

SELECT tbl_Tutorial.StudentID, Count(tbl_Tutorial.TutorialID) AS CountOfTutorialID
FROM tbl_Tutorial
GROUP BY tbl_Tutorial.StudentID 
HAVING Count(tbl_Tutorial.TutorialID) >1;

 

[LPurvis]

>> "... 3 minutes is a long time!! ... what took you so long!"

Ah but count the words man... Count the words!!

 

[datAdrenaline]

>> I would appreciate a step by step instruction so I can learn from this. <<

The way Count() works has been hilited well by Leigh ... so in explaining the SQL statment I proposed:

... the SUB-query will return the studentID's that have a Tutorial count of greater than one.

Then the MAIN query will count the number of StudentID's (effectly the records) that the SUB-query returns...

Does that make sense? ... Do you know which one is the SUB query and the MAIN query? (I am not asking that to sound brash ... just trying to get a feel for "where your at")

 

[datAdrenaline]

>> Ah but count the words man... Count the words!! <<

... DOH!!! ... I was rushed by my real job!! ... work ... it really gets in the way of fun eh?