Query of Attendance Summary Stats

[Kayleigh]

I'm struggling to calculate the summary attendance statistics in my database. I have taken out the relevant tables - see below.
Basically, I need a total for each student of how many dates of lateness/absence/attendances they have in total. (these can each be in a separate query.) Also the same for AM and PM. (At the moment I have managed to get a summary of sessions attended but it will include a date multiple times if a different attendance code is used.)
Register types are the groups of attendance figures to base the summaries on.

 

[conception_native_0123]

the first thing you need I think is a relationship between tblStudents and the attendance table. your register type table contains the lateness so that should alaso be connected.

 

[plog]

I need a total for each student of how many dates of lateness/absence/attendances they have in total. (these can each be in a separate query.) Also the same for AM and PM

It is not intuitive from your database what the definition of lateness, absence and attendances is. You need to define that for use in terms of your data.

Ideally you should demonstrate it with data. You've got 127 records in your jtblStudentAttendance, I suggest you show us what exactly the data should be returned from all the queries you envision using those 127 records. Show us what data you expect to end up with.

 

[Kayleigh]

I apologise for my lack of clarity in the above query. The data is quite tricky and I have several statistics which I must produce.
To start, I will explain the lateness or absence (authorised and unauthorised separate) figures:
Every day each student is marked for at least one session in morning and at least one session in afternoon. The register codes indicate if they attended.
Currently, I have a form to display a summary of the student's attendance by date (AM and PM separate) and this also be enlarged to view the sessions on that date.
I also need to display a summary of the number of absences in AM and PM filtered by dates of current term (displayed on form), as well as the total latenesses. This is a little trickier since I would have to lookup each register code and check in register types table if it is classed as a lateness and then total all these. However several sessions may be marked as late so it must group by date so it only appears once either AM/PM.
A similar calculation is necessary for authorised and unauthorised absences - to use the register types to class the register code.

See some screenshots of summaries enclosed.
Hope this makes more sense.

 

[plog]

Nope, more confusing. Pick 1, let's work on just 1 query.

You have data in the database you uploaded, that's the starting data. Now, using that data, show me what you expect a query to return for one of your queries. Manually work through it and show me what you expect the query to produce when you feed it the data from the database you uploaded.

 

[Auntiejack56]

You ask for "number of absences in AM and PM".
In your registry table, you have 'Present at AM registration' and 'Present at PM registration', which makes it easy to work out AM or PM registration. But there is no AM/PM equivalent for late or absent. Late is just "Late but arrived before the register closed" and "Late but arrived after the register closed". So are you going to work out AM/PM from the session start time?
I suspect you want something like this:

SELECT l.fldDate, IIf([fldSLStart]>#12/30/1899 12:0:0#,"PM","AM") AS AMPM, c.fldRegisterDefinition, Count(l.fldSessionLogID) AS CountOffldSessionLogID
FROM (jtblSessionLog AS l INNER JOIN jtblStudentAttendance AS a ON l.fldSessionLogID = a.fldSessionLogID) INNER JOIN tblRegisterCodes AS c ON a.fldRegisterCodeID = c.fldRegisterCodeID
GROUP BY l.fldDate, IIf([fldSLStart]>#12/30/1899 12:0:0#,"PM","AM"), c.fldRegisterDefinition
HAVING (((l.fldDate) Between #6/1/2021# And #6/30/2021#));

If it is possible to have a mixture of registrations for the same student in the AM or PM, then it is getting more complicated. This might get you started though?
I used 1st June to 30 June as though it was a term period, but of course you would use the correct dates. I'm unclear about how the immediate if statement works, but it does so let's leave it at that, shall we? Brainier types can comment.
Jack

 

[Kayleigh]

So based on @Auntiejack56 's comment, an idea may be to have two base queries - am and pm, based on session start and end times. Then create queries summarising lateness/absence grouped by date and this should result in a summary for both AM and PM?
But it seems that I would need a catch where different codes used for sessions within one date - maybe Max?

 

[Auntiejack56]

This is where I find the users of the system start to show some brittleness. If you were to ask them what they wanted to see where different codes are used for sessions within the one date, I suspect there would be disagreement.
If you have one main user, that simplifies things. They can guide you as to exactly what they want, possibly just as you've suggested. That is, adding another numeric field for Priority and then doing a Max on that.
But in my experience, you are just about to tiptoe across a minefield. Best of luck.

 

[Pat Hartman]

As the others have alluded to, your schema does not support your desired results. A database cannot show you any data it simply does not have.

Although it is not wrong to have multi-field primary keys (Access allows up to 10 columns in an index), you will find that joins are simpler with autonumbers. Rather than making a multi-field PK, make a multi-field unique index to enforce business logic and use an autonumber for simplicity.

PS - prefixing every column name with fld just makes you type three more characters before intellisense kicks in. It isn't productive. It also reduces the meaningfulness of what you can see when you open the table in DS view. All you see is a row of "fld"'s. That's enlightening.

We prefix table names and query names because they are used interchangeably and you may need to know which you are working with. Same for Forms and Reports.

 

[isladogs]

@Krayna
I agree with the previous comments about fields and table structure.
The attendance codes in tblRegisterCodes largely match those in my commercial schools databases so I'm guessing you may be UK based.

You may find it useful to look at the Attendance features in the free DEMO version of my School Data Analyser application