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

[clatham]

Hi all,

I have a table of Students and a table of attendance. For tblAttendance I have the following fields:

Student ID (Linked to tblStudents)
Attendance Date
Attendance Code (Session 1)
Attendance Code (Session 2)
Attendance Code (Session 3)
Attendance Code (Session 4)

For each session, students can be marked P (present), L (late) or various absence codes.

I need to find a way to total up for each Student ID the amount of times they were present (including is they were late), and I am stumped as to how to do this.

I'm okay with general Access, but I don't know about coding, so hoping it's possible to do this either in a query or to have some guidance on coding.

TIA,
CLATHAM

 

[Gasman]

If you had a structure like

Student ID (Linked to tblStudents)
Attendance Date
SessionNumber
Attendance Code

Then you could easily count the different attendances.?

 

[clatham]

Sorry, I don't think I made it very clear.

For each Attendance Date each Student ID needs to have an Attendance Code for all 4 sessions.

Also, I realise I missed out the other field in my table: Attendance ID (Auto number)

Does that make sense?

 

[plog]

You need to fix your table. You are storing data in field names. Instead, you need to store that data in the table itself. This should be your table structure:

StudentID
AttendanceDate
AttendanceSession
AttendanceCode

The session number goes into AttendanceSession. That way instead of 1 record holding 4 sessions of data, you will have 4 records (1 for each session). Then with that proper structure this becomes a trivial aggregate query:

SELECT StudentID, COUNT(StudentID)
FROM YourTableNameHere
WHERE AttendanceCode='P'
GROUP BY StudentID

Additionally, do not use spaces in field names--just makes coding and querying that more difficult.

 

[clatham]

Oh I see, thank you both. I don't suppose there is a quick way I can change the data from the four fields to fit the new table structure? I'm going to have to manually populate?

Will update my field names.

 

[Gasman]

Rename the old table, and create a new table with the structure recommended.
Then create an append query to take the data from the old table for each session and run 4 times, once for each session, using each of the fields in turn.

Use the Query GUI to make it easier to do. You would not even need to save the query, just amend and run as required.

HTH

 

[isladogs]

Create new table as described in posts 2 & 3.
Then use an append query to add new records for one session at a time
So you will end up with 4x the previous records.
When done, you should delete the original table.

 

[clatham]

I now have an issue with my report and I can't get it to show up correctly following the change in table structure.

Do I post the issue here or in a new thread?

 

[isladogs]

Here is fine.
You will need to redesign your report to fit the new table structure.
If you want four separate columns for each of the sessions, you will need to create a crosstab query and use that for your report.

 

[clatham]

Thanks, it sounds like that is what I need, but I'm not sure how to do that as I've never used crosstab (I'm more of a novice than I thought!!)

So I assume I put AttendanceDate as the Row Heading, AttendanceSession as the Column Heading, but I'm not sure what to put for the Value?

 

[clatham]

Okay I think I have it, I've put StudentID as another Row Heading and AttendanceCode as the Value with Total set to First.

It looks as though that's right.

 

[Gasman]

Use the Query Wizard. That will walk you through the steps.

Thanks, it sounds like that is what I need, but I'm not sure how to do that as I've never used crosstab (I'm more of a novice than I thought!!)

So I assume I put AttendanceDate as the Row Heading, AttendanceSession as the Column Heading, but I'm not sure what to put for the Value?

 

[isladogs]

Okay I think I have it, I've put StudentID as another Row Heading and AttendanceCode as the Value with Total set to First.

It looks as though that's right.

Yes it sounds correct for column header and value. As Gasman said, use the crosstab query wizard will help a lot. The only restriction the wizard has is a limit of 3 row headers but you can add more manually.

So you might want 4 row headers StudentID, LastName, FirstName, AttendanceDate.

The wizard will add a totals column by default. If you don't want it, just delete it

 

[clatham]

Then with that proper structure this becomes a trivial aggregate query:

SELECT StudentID, COUNT(StudentID)
FROM YourTableNameHere
WHERE AttendanceCode='P'
GROUP BY StudentID

So I've done this, and I've got two queries, one which counts the number of "P" or "L" grouped by StudentID, and a second which counts the total AttendanceSessions.

When I try to pull the two queries together to get a list of StudentID, SessionsAttended, TotalSessions I can't get it to work. I end up with multiple listings of the StudentID and I can't figure out why or how to fix it.

Eventually I need to get to a point where I can calculate the percentage of sessions that each student has attended, but as the session information is added weekly it needs to be dynamic, hence totalling the AttendanceSessions.

Starting to feel a bit useless at this now!

 

[isladogs]

Suggest you post the SQL of both queries and the combined version.

Also have a look at this parallel thread on the same topic https://www.access-programmers.co.uk/forums/showthread.php?t=301692

 

[clatham]

Query 1:

SELECT tblAttendance.StudentID, Count(tblAttendance.StudentID) AS CountOfStudentID
FROM tblAttendance
WHERE (((tblAttendance.AttendanceCode)="P" Or (tblAttendance.AttendanceCode)="L"))
GROUP BY tblAttendance.StudentID;

Query 2:

SELECT tblAttendance.StudentID, Count(tblAttendance.StudentID) AS CountOfStudentID
FROM tblAttendance
WHERE (((tblAttendance.AttendanceCode)="P" Or (tblAttendance.AttendanceCode)="L"))
GROUP BY tblAttendance.StudentID;

Joint query:

SELECT qryTotalSessions.StudentID, qryTotalSessions.CountOfStudentID, qryTotalSessionsAttended.CountOfStudentID
FROM qryTotalSessions, qryTotalSessionsAttended, tblStudents;

The Lesson Attendance Register on the other thread is just beautiful, That's what I'm striving for as an input form, once the back end is sorted out!

 

[plog]

Query 1 and Query 2 are exactly the same. The Joint query is a cartesian product--its going to produce an enormous amount of rows because you haven't joined your tables.

How about you post sample data to demonstrate what you want. Post 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 what data you hope your query will produce when you feed it the data from A.

 

[clatham]

Sorry, I managed to paste the same SQL twice, but it's only very slightly different... Query 2:
SELECT tblAttendance.StudentID, Count(tblAttendance.StudentID) AS CountOfStudentID
FROM tblAttendance
WHERE (((tblAttendance.AttendanceCode) Is Not Null))
GROUP BY tblAttendance.StudentID;

Table Structure:
tblStudents
StudentID
FirstName
Surname
TrustID - linked to tblTrusts
CohortID

tblAttendance
StudentID - linked to tblStudents
ModuleID - linked to tblModules
AttendanceDate
AttendanceSession
AttendanceCode

Example Data:
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
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 L
234567 1 09/02/2018 2 P


What I need is a query that totals the amount of sessions that a student could have possibly attended and totals the amount of times they actually attended (i.e P or L). From the data above I would expect an output such as

Student ID - 123456
TotalSessions - 6
SessionsAttended - 4

StudentID - 234567
TotalSessions - 6
SessionsAttended - 6

Sorry the alignment is out on the Example Data, hope this makes sense?

 

[plog]

This SQL will produce your results:

SELECT StudentID, COUNT(StudentID) AS TotalSessions, SUM(Iif(AttendanceCode='P' OR AttendanceCode='L', 1,0)) AS SessionsAttended
FROM tblAttendance
GROUP BY StudentID

 

[clatham]

Thank you so much, I would never have got there on my own. I need to learn more about SQL!