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:
And finally, here's a better example. This time a standard (non-crosstab) aggregate query showing an expression on the field Demerits:
isladogs thank you for your response. Now I understand. I've simplified the example. If we have a table of Employees like this:
and we need e.g. to calculate SUM of turnover with VAT for every project I can use the following query (with Expression):
That's it :).
- Glad you have a working solution
