How to stack in a text field?

Question

Microsoft Access

Hello! I made a cross query in which you need to specify the total values.
Request:

TRANSFORM Min(last.absent) AS [Min-absent]
SELECT last.studentid, Min(last.absent) AS Total_Absents, Min(last.absent) AS Total_Lates
FROM [last]
GROUP BY last.studentid
PIVOT last.am_day;

It is necessary to add all "-1" in one final field, and all "L" in the second.
Sum does not work in this case, so far I have set Min.

Answer 1

With the help of IIF () you can catch the desired values.
https://docs.microsoft.com/en-us/sql/t-sql/functio...

TRANSFORM Min(last.absent) AS [Min-absent]
SELECT last.studentid, SUM(IIF(last.absent="-1", 1, 0)) AS Total_Absents, SUM(IIF(last.absent="L", 1, 0)) AS Total_Lates
FROM [last]
GROUP BY last.studentid
PIVOT last.am_day;