Query Question - Count

Results 1 to 4 of 4

Thread: Query Question - Count

  1. #1
    Michelle Brown Guest

    Default Query Question - Count

    Hi, I hope this isnt too OT for this forum.<BR><BR>I have a table called students, which has a students ID, name, and the ID of an Interest Session they would like to attend.<BR><BR>I have a table called interestsession that has the ID and a descriptions.<BR><BR>I want to write a query that will give me a summary of the count for each session.<BR><BR>so, like it would say in a table with 2 columns:<BR>Interest Session ID &#124 Count of all records where Student.ID = InterestSession.ID <BR><BR>Can anyone help me?

  2. #2
    Michelle Brown Guest

    Default and I am using MS Access 2000

    and I am using Access 2000...

  3. #3
    Join Date
    Dec 1969

    Default Moderately complex SQL...fun stuff!

    &nbsp;<BR>&#062; Hi, I hope this isnt too OT for this forum.<BR><BR>This is 100% ON TOPIC for this forum! Right on the money!<BR><BR>**************<BR><BR>In your question, you say the students table has a field named ID for the student id and a field named ID for the session ID. (Well, you don&#039t say that, but you don&#039t make it clear what the actual names are.) So I will use fields named Students.StudentID and Students.SessID for clarity, okay?<BR><BR>The other thing that I *think* is a mistake on your part. As your question is written, there is no need to query the InterestSession table in order to produce the desired results. Hmmmm...UNLESS you are wanting to be sure to get a zero value for sessions that have no interested students?<BR><BR>I would presume that what you *really* want is InterestSession.ID *and* Interest.Session.Description to appear in the output, to make it more readable. In which case you *do* need to access the second table. <BR><BR>********************<BR><BR>&#060;%<BR>... . you get to create and open the connection object ...<BR><BR>SQL = "SELECT Sess.ID AS sID, Sess.Description AS sDesc, Count(Students.StudentID) AS HowMany" _<BR> &nbsp; &nbsp; &nbsp; & " FROM InterestSession AS Sess INNER JOIN Students" _<BR> &nbsp; &nbsp; &nbsp; & " ON Sess.ID = Students.SessID" _<BR> &nbsp; &nbsp; &nbsp; & " GROUP BY Sess.ID, Sess.Description"<BR><BR>Set RS = yourConnection.Execute( SQL )<BR>%&#062;<BR><BR>&#060;TABLE Size="100%"&#062;<BR>&#060;TR&#062;&#060;TH&#062;S ession ID&#060;/TH&#062;&#060;TH&#062;Session Description&#060;/TH&#062;&#060;TH&#062;Number Interested&#060;/TH&#062;&#060;/TR&#062;<BR>&#060;% Do Until RS.EOF %&#062;<BR> &nbsp; &nbsp; &#060;TR&#062;<BR> &nbsp; &nbsp; &nbsp; &nbsp; &#060;TD&#062;&#060;% = RS("sID") %&#062;&#060;/TD&#062;<BR> &nbsp; &nbsp; &nbsp; &nbsp; &#060;TD&#062;&#060;% = RS("sDesc") %&#062;&#060;/TD&#062;<BR> &nbsp; &nbsp; &nbsp; &nbsp; &#060;TD&#062;&#060;% = RS("howMany") %&#062;&#060;/TD&#062;<BR> &nbsp; &nbsp; &#060;/TR&#062;<BR>&#060;% <BR> &nbsp; &nbsp; RS.MoveNext <BR>Loop<BR>%&#062;<BR><BR>I&#039m pretty sure that&#039s all correct. It tested it on a similar DB that I happened to have lying around (posters to a message board by visitor id and visitor name, using a count of messages posted instead of the message ids) and it worked just fine.<BR><BR>What it *DOES NOT DO* is report those sessions that generated 0 interest. That&#039s tougher to do. I think it needs to be a LEFT OUTER JOIN instead of inner.<BR><BR>Oh, never mind. I decided to go try it! Yes, if you simply change the words "INNER JOIN" in the above to "LEFT OUTER JOIN" it works!<BR><BR>You can even add an ORDER BY to put things in "howMany" order! Though Access doesn&#039t seem to allow the "howMany" alias in the SQL, so you have to code:<BR><BR>SQL = "SELECT Sess.ID AS sID, Sess.Description AS sDesc, Count(Students.StudentID) AS HowMany" _<BR> &nbsp; &nbsp; &nbsp; & " FROM InterestSession AS Sess LEFT OUTER JOIN Students" _<BR> &nbsp; &nbsp; &nbsp; & " ON Sess.ID = Students.SessID" _<BR> &nbsp; &nbsp; &nbsp; & " GROUP BY Sess.ID, Sess.Description" _<BR> &nbsp; &nbsp; &nbsp; & " ORDER BY Count(Students.StudentID) DESC"<BR><BR>At least that worked for me with Access 97!<BR><BR>Have fun!<BR><BR>p.s.: The very last line of SQL there points out why I named the field "Description" instead of just "Desc". Because "Desc" is a keyword in SQL, you shouldn&#039t use it as a field name. Hokay?<BR><BR><BR>

  4. #4
    Michelle Brown Guest

    Default RE: Query Question - Count

    Thanks Bill! You rock!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts