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.
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
GROUP BY ClassAttend.StudentID;
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
GROUP BY tblStudentAttendanceIndiv.StudentID;
I am getting an error:
Characters found after end of SQL Statement.
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.
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:
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.
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
GROUP BY tblStudentAttendanceIndiv.StudentID, ([ClassCount]*100)/[Attend];
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.
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.