Calculate most occurring value

[Kayleigh]

Hi,
I'm back to work with my attendance database. The client requires more data manipulation and I'm really struggling to find the best way to achieve it.

The requirements are:
For each week (commencing sunday), find overall average of attendance for each student.
Currently we have each sessions' attendance data but must split each day into two - overall AM and PM. I have done this and now have two queries of summary data.

They also want a student to be marked as present even if they only attended one lesson in session - so basically I would need to find highest occurring value for each day and student in each query.
I managed to use the Min aggregate to get it almost working however it's not 100% accurate - usually min value is same as highest occurring value (because first attendance code is present so usually most of sessions are marked with this) but it may not always be the case. Can someone help me find the correct way to do this please. (I see a useful thread here but it is not sufficiently detailed for my scenario: https://www.access-programmers.co.uk/forums/threads/find-the-most-frequent-value.63008/)

Here is the SQL of what I have managed so far:

SELECT qryAllStudentAttendanceRegCodesAM.fldStudentID, qryAllStudentAttendanceRegCodesAM.fldDate, Min(qryAllStudentAttendanceRegCodesAM.fldRegisterTypeID) AS MinOffldRegisterTypeID, Min(qryAllStudentAttendanceRegCodesAM.fldRegisterCodeID) AS MinOffldRegisterCodeID
FROM qryAllStudentAttendanceRegCodesAM
GROUP BY qryAllStudentAttendanceRegCodesAM.fldStudentID, qryAllStudentAttendanceRegCodesAM.fldDate;

Also regarding the average aspect, can anyone advise a fair way to calculate so every student is given same chance ie if there are ten sessions a week (AM + PM x 5) each student's average should be calculated out ten (even if they don't have an attendance record for this session). Does this make sense?

Best,
Kayleigh

 

[plog]

Its best to demonstrate data issues with sample data. Please provide 2 sets:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed in the data from A.

Again, 2 sets of data, starting and ending where the ending is the actual results based on the starting.

 

[Kayleigh]

Its more data than you think but I will try!

There are two connected tables with attendance data - sessionLog and studentAttendance. There is a simple query joining and filtering the data. Here is some sample data:
qryAllStudentAttendanceDates

fldStudentID​	fldDate​	fldAttended​	fldRegisterCodeID​	fldSessionID​	fldSLStart​	cfMonth​
293​	02/01/2023​	Yes​	42​	2228​	12:00:00​	1​
306​	02/01/2023​	Yes​	42​	2231​	11:00:00​	1​
256​	02/01/2023​	Yes​	42​	2229​	11:30:00​	1​
293​	02/01/2023​	Yes​	42​	2228​	12:00:00​	1​
306​	02/01/2023​	Yes​	42​	2231​	11:00:00​	1​
256​	02/01/2023​	Yes​	42​	2229​	11:30:00​	1​
256​	03/01/2023​	No​	28​	2280​	12:10:00​	1​
297​	03/01/2023​	No​	32​	​	09:00:00​	1​
241​	03/01/2023​	No​	28​	2134​	12:10:00​	1​
305​	03/01/2023​	No​	32​	​	09:00:00​	1​
248​	03/01/2023​	No​	28​	2186​	12:00:00​	1​
244​	03/01/2023​	No​	29​	2199​	13:15:00​	1​
246​	03/01/2023​	No​	29​	2146​	13:30:00​	1​

An end result (which I have partially achieved albeit not 100% accurate) is below:
qryWeeklyAttendanceAverages

fldStudentID​	01/01/2023​	08/01/2023​
234​	100.00%​	100.00%​
237​	100.00%​	​
239​	100.00%​	100.00%​
240​	100.00%​	100.00%​
241​	100.00%​	100.00%​
244​	100.00%​	50.00%​
245​	100.00%​	​
246​	100.00%​	100.00%​
248​	100.00%​	100.00%​
250​	100.00%​	100.00%​
251​	100.00%​	100.00%​
252​	100.00%​	0.00%​
256​	87.50%​	50.00%​
290​	85.71%​	100.00%​
291​	100.00%​	100.00%​
292​	100.00%​	​
293​	87.50%​	100.00%​
294​	100.00%​	100.00%​
295​	85.71%​	100.00%​
297​	100.00%​	100.00%​

What is happening between the two sets of data is:

1. The first filter query to only show sessions from current month
2. 2x query to filter AM/PM sessions and summarise by date and student
3. 2x Aggregate query for AM/PM, summarises by min register type (ie should display lowest num) and by min register code - but this is NOT correct - should be highest occurring register code.
4. Union to join AM and PM queries, also calculates week commencing and 1 or 0 depending on if summary figure is present or absent code
5. Crosstab to display student by date and average aggregate of attendance per week

If you want a breakdown of all the queries involved happy to share.

 

[plog]

You've built a rube golberg machine of queries and want me to add a new gizmo to the end of it. That's not easy nor efficient.

I want starting data from your tables and expected results based on that starting data. That means your expected results need to be based on the startind data you provide. I see fldStudentID=234 in the results, but no fldStudentID=234 in the starting data. The B data you give me needs to be fully formed from the A data you provide. That's the only way I can see what you need to occur.

 

[June7]

Or provide db for analysis. Follow instructions at bottom of my post.

 

[ebs17]

The first filter query to only show sessions from current month

There's already an error in there. A week can go over a month change.

fldSLStart = 11:00:00
What means that? The student was present before noon and after noon, although maybe he left 11:33:00? The necessary information is missing here. Such information is best found in the tables, as @plog correctly says.

 

[isladogs]

As well as the important points raised above, there are 2 distinct sets of attendance data involved here:
a) session attendance (AM/PM) - normally a legal requirement
b) lesson attendance - normally for internal use only by the teacher / school

The two sets of data should be stored in separate tables - they aren't duplicates
You shouldn't normally be using the lesson attendance in order to determine the session attendance via queries

However one exception to this from my own experience of working with school attendance data for many years
For students who missed the morning registration but arrived for the first lesson, the system generated an update query which changed their session attendance mark from absent (N) to late (L) or another appropriate code such as M (medical appointment).

 

[Pat Hartman]

Do not include the session details in the count query.

Just select studentID and date and use Group by for both so you only get one record for the day. Then build a query that uses that data and averages the sessions by week. Use the built in week number if that works for you. Otherwise, you will need to create a custom table with WeekNumber, StartDate, EndDate to create your own weeks.

 

[June7]

Or instead of week number, calculate the date of Sunday for the week each date falls within and use that Sunday date to group records.

date()-weekday(date())+1

fldDate-weekday(fldDate)+1

This will allow for each week to have 7 days even when they cross month or year.

If you don't want to cross month or year then can use week number. DatePart("WW", fldDate)

 

[ebs17]

Currently we have each sessions' attendance data but must split each day into two - overall AM and PM.

How do you get a split into AM and PM with days ignoring times? Multiply by 2?