Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    63

    Unanswered: Query total of records that fit the criteria

    Hello all,

    I am trying to sort out a query.
    There is a table [tblStudentAttendacneIndiv] that holds this data:
    StudentID: Integer
    SessionDate: Date
    Attended: Yes/No

    Via a subForm elsewhere, the date and attended fields are assigned. The StudentID field auto matches an autoNumber field from another table [tblStudents].

    I need the query to return the number of Yes from the [Attended] field out of the number of Total records for each [StudentID]. This is then turned into my percentage in a final column.

    I can do it, but it only returns the percentage for the first [StudentID] IE [SudentID] = 1.

    Can anyone tell me what I am doing wrong?

    Here is my Query code.

    Code:
    StudentID
    tblStudentAttendanceIndiv
    
    StudentID
    tblStudents
    
    Attended
    tblStudentAttendanceIndiv
    
    AttendedCount: DCount("[StudentID]","tblStudentAttendanceIndiv","[Attended] <> 0")
    
    SessionCount: DCount("[StudentID]","tblStudentAttendanceIndiv","[Attended] <> 0" And "[Attended] = 0")
    
    PercentAttendance: ([AttendedCount]*100)/[SessionCount]
    Thanks,
    ChrisOfCatford
    Last edited by ChrisOfCatford; 10-27-04 at 12:42. Reason: Mistake in code

  2. #2
    Join Date
    Sep 2004
    Posts
    63
    Sorry, just reallised part of what is going on. It is doing all the records, regardless of the [StudentID].
    How can I change the code to make it just do the one?

    Results would need to look like
    1 = 54%
    2 = 100%
    and so on.

    Thanks,
    ChrisOfCatford

  3. #3
    Join Date
    Sep 2004
    Posts
    63
    OK, Maybe I am completely going about it the wrong way... Does anyone know how I can do this? It is just counting records with a particular ID in a table and then counting those that say yes in another field.

    This MUST be possible... Surely?

    Thanks,
    ChrisOfCatford

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Assume the table is called 'ClassAttend' and it includes fields for the StudentID, each class date as 'ClassDate', and a Yes/No field called 'Attended'. This should work:

    SELECT ClassAttend.StudentID, Count(ClassAttend.ClassDate) AS ClassCount, Sum(IIf([Attended]=Yes,1,0)) AS Attend, round([attend]/[classcount]*100,1) AS AttendPercent
    FROM ClassAttend
    GROUP BY ClassAttend.StudentID;


    TD

  5. #5
    Join Date
    Sep 2004
    Posts
    63
    Hi TD,

    Thanks for your time. I used your query code, changing where relevant and now it looks like this:

    SELECT tblStudentAttendanceIndiv.StudentID, Count(tblStudentAttendanceIndiv.SessionDate) AS ClassCount, Sum(IIf([Attended]=Yes,1,0)) AS Attend, round([Attend]/[ClassCount]*100,1) AS AttendPercent
    FROM tblStudentAttendanceIndiv
    GROUP BY tblStudentAttendanceIndiv.StudentID;

    I am getting an error:
    Characters found after end of SQL Statement.

  6. #6
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    I'm not sure why you would be getting that. I actually developed this as a Select Query within Access and then output the SQL so I could paste it into here and it worked. The one thing that may need to be addressed is where I have 'Sum(IIf([Attended]=Yes' may need to be 'Sum(IIf([ClassAttend]![Attended]=Yes', and then you would need to change [ClassAttend]! to tblStudentAttendanceIndiv. and change [Attended] to whatever you have called your yes/no field.

    I hope that does the trick.

  7. #7
    Join Date
    Sep 2004
    Posts
    63
    Thanks TD, that mostly works now. I was adding your SQL to the end of an existing query. Sorry, I am not used to that view.

    I am just stuck on the percentage bit. I got an error You tried to use.....as part of an aggregate function... I am paraphrasing because I have changed the code so that it doesn't do that. It was concerned with just the last bit, used to get my percentages.

    I tried a simple one like that in my first code:
    TotalAttend: ([ClassCount]*100)/[Attend]

    But when I do that it asks for both values. I thought that they were defined in the previous columns. This is frustrating because I can see the values in the two columns but I can't seem to use them.

    Any ideas?

    Thanks,
    ChrisOfCatford
    My whole SQL so far:
    SELECT tblStudentAttendanceIndiv.StudentID, Count(tblStudentAttendanceIndiv.SessionDate) AS ClassCount, Sum(IIf([Attended]=Yes,1,0)) AS Attend, ([ClassCount]*100)/[Attend] AS TotalAttend
    FROM tblStudentAttendanceIndiv
    GROUP BY tblStudentAttendanceIndiv.StudentID, ([ClassCount]*100)/[Attend];

  8. #8
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    I'm not sure why it isn't working. As I said, I designed this in a Select Query and it worked and then I just viewed the SQL so I could copy and paste it here. Here is a very small database with the single table with 48 records and then a query that outputs the attendance percentages. I zipped the file so it is very small.
    Attached Files Attached Files

  9. #9
    Join Date
    Sep 2004
    Posts
    63
    Hi TD,

    Thanks for posting your sample. I am not with the DB at the mo so I will check it over later. Presumably I have just changed one of my fields incorrectly, I will post if I get it to work.
    I managed to use a form field to do the calc for the moment, which saves my sanity, but eventually to output to report I will need to get your version working really.

    Thanks again,
    ChrisOfCatford

  10. #10
    Join Date
    Sep 2004
    Posts
    63
    Hi once more,

    Sorted. It was again attention to detail that was needed. I had not set it as an expression so it did not work. That setting changed, the whole thing works just as it should.

    Many thanks,
    ChrisOfCatford

Posting Permissions

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