Forum Discussion
Real example for Total:Expression in query
Hi guys, I would like to know how Total:Expression works in real life I can't find a real example. Can you give me a very simple example please?
In an aggregate (totals) query, you would use expression for any field you wish to display that is not being used for grouping and where options such as Sum/Count/Max/first aren't appropriate.
For example, in this crosstab query based on aggregated fields:
TRANSFORM First(qryCountAttendanceMarksByDateSessionPeriod.CountOfAttMarkID) AS FirstOfCountOfAttMarkID
SELECT SchCalendar.DayDate AS [Date], SchCalendar.SessionDay AS [Day], SchCalendar.ValidAM AS [School Day], SchCalendar.WeekNumber, SchCalendar.TermNumber, Sum(qryCountAttendanceMarksByDateSessionPeriod.CountOfAttMarkID) AS [Total Records]
FROM SchCalendar LEFT JOIN qryCountAttendanceMarksByDateSessionPeriod ON SchCalendar.DayDate = qryCountAttendanceMarksByDateSessionPeriod.Date
WHERE (((SchCalendar.SessionDay) Not Like 'S*') AND ((SchCalendar.WeekNumber) Is Not Null) AND ((SchCalendar.TermNumber) Is Not Null))
GROUP BY SchCalendar.TermNumber, SchCalendar.DayDate, SchCalendar.SessionDay, SchCalendar.ValidAM, SchCalendar.WeekNumber, SchCalendar.TermNumber, SchCalendar.CycleDayNumber
ORDER BY SchCalendar.TermNumber, SchCalendar.DayDate
PIVOT qryCountAttendanceMarksByDateSessionPeriod.[AM/PM];See attached screenshot: