Query to count multiple criteria split by record (1 Viewer)

[clatham]

I've just realised this is not quite doing what I need it to do. In some cases I have AttendanceDates with no AttendanceCodes, in these cases I would need the query to not include that Dates/Sessions in the total:

StudentID ModuleID AttendanceDate AttendaceSession AttendanceCode
123456 1 02/02/2018 1 P
123456 1 02/02/2018 2 P
123456 1 02/02/2018 3 P
123456 1 02/02/2018 4 A
123456 1 09/02/2018 1 L
123456 1 09/02/2018 2 A
123456 1 09/02/2018 3
123456 1 09/02/2018 4
234567 1 02/02/2018 1 P
234567 1 02/02/2018 2 P
234567 1 02/02/2018 3 P
234567 1 02/02/2018 4 P
234567 1 09/02/2018 1 A
234567 1 09/02/2018 2 A
234567 1 09/02/2018 3
234567 1 09/02/2018 4


Student ID - 123456
TotalSessions - 6
SessionsAttended - 4

StudentID - 234567
TotalSessions - 6
SessionsAttended - 6

 

[clatham]

I've changed the code to:
SELECT tblAttendance.StudentID, tblStudents.FirstName, tblStudents.Surname, Sum(IIf(AttendanceCode='P' Or AttendanceCode='A' Or AttendanceCode='T' Or AttendanceCode='M' Or AttendanceCode='AA' Or AttendanceCode='AL' Or AttendanceCode='I' Or AttendanceCode='L',1,0)) AS TotalSessions, Sum(IIf(AttendanceCode='P' Or AttendanceCode='L',1,0)) AS SessionsAttended, [SessionsAttended]/[TotalSessions] AS AttendacePercentage
FROM tblStudents INNER JOIN tblAttendance ON tblStudents.StudentID = tblAttendance.StudentID
GROUP BY tblAttendance.StudentID, tblStudents.FirstName, tblStudents.Surname, [SessionsAttended]/[TotalSessions]
ORDER BY tblStudents.FirstName;


Which is now working.

 

[plog]

You should have an AttendanceCode table instead of those long OR expressions. That table will tell you what each code does and contain a field to designate that a person attended or not.

 

[isladogs]

You should have an AttendanceCode table instead of those long OR expressions. That table will tell you what each code does and contain a field to designate that a person attended or not.

Yes I agree. In case it helps, I've attached my AttendanceCodes table in Excel format. This has the official UK attendance codes

 

[clatham]

Thanks both.

I have tblAttendanceCodes, but I will add a new field to simplify between Present/Absent for this qry.

 

[clatham]

I have a problem with my crosstab query that the 'Recordset is not updateable'

Code is below, I assume (from reading up on it) that there's an issue with a missing foreign key, but I can't see where I would add it. Table Structure attached.

TRANSFORM First(tblAttendance.AttendanceCode) AS FirstOfAttendanceCode
SELECT tblAttendance.ModuleID, tblStudents.TrustID, tblAttendance.StudentID, tblAttendance.AttendanceDate, tblStudents.CohortID, tblStudents.FirstName, tblStudents.Surname
FROM tblStudents INNER JOIN tblAttendance ON tblStudents.StudentID = tblAttendance.StudentID
GROUP BY tblAttendance.ModuleID, tblStudents.TrustID, tblAttendance.StudentID, tblAttendance.AttendanceDate, tblStudents.CohortID, tblStudents.FirstName, tblStudents.Surname
ORDER BY tblAttendance.AttendanceDate, tblStudents.CohortID, tblStudents.FirstName
PIVOT tblAttendance.AttendanceSession;

 

[isladogs]

Due to their structure, crosstab queries are ALWAYS read only.
If you need an editable query, a different approach is necessary.

For info, as you are still asking questions in this thread, I have removed the SOLVED tag.

EDIT: Just looked at your relationship diagram
There should only be one connecting path betwwen tables
Remove the link between CohortID in tblCohort & tblAttendance
Add a link between CohortID in tblStudents & tblAttendance

However your crosstab query will still not be editable

 

[clatham]

I've been trying to find a way around this, but I can't come up with an option for creating an easy to use input form to add new data to the tblAttendance. The raw data in tblAttendancewould be far too difficult for my data-inputter to navigate to enter what is needed.

PLEASE HELP!

 

[plog]

tblAttendance is a junction table--meaning it facilitates a many to many relationship between 2 other tables (Modules and Students). The best way to navigate the many side of a relationship in a form is to make it a subform on what it relates to. That means tblAttendance can be a subform on either the Module form or the Student form. That decision of which is up to you based on how you expect users to input data.

In either case, here's the broads strokes of what the workflow should be like if the user uses the Module side method:

User selects a module--either in a drop down, or from a form which lists them all. This takes them to a page which shows just the data of that one module. On that page is a subform for tblAttendance which is a continous form and shows everything related to that Module. The user can then use drop downs to choose students to add and inputs the Session and Code and other data that goes into tblAttendance.

 

[clatham]

That's great - thank you, I've got that working now.

Is it also possible to add in the summary data from a query to be viewed on the form, other than having it as a subform where it looks like a table?

As above, I've created a query which looks at the total number of sessions a student could have attended then works out the percentage of sessions they did attend. I would like to display this on the student's record when looking at it in frmStudent - if possible.