Real example for Total:Expression in query

Question

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?&nbsp;

isladogs · Answer

JanZitniak&nbsp;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:&nbsp;

isladogs · Answer

isladogs&nbsp;Apologies. This is the screenshot of the query design&nbsp;&nbsp;

isladogs · Answer

And finally, here's a better example. This time a standard (non-crosstab) aggregate query showing an expression on the field Demerits:&nbsp;&nbsp;

janzitniak · Answer

isladogs&nbsp;thank you for your response. Now I understand. I've simplified the example. If we have a table of Employees like this:&nbsp;&nbsp;and we need e.g. to calculate SUM of turnover with VAT for every project I can use the following query (with Expression):&nbsp;&nbsp;That's it :).&nbsp;

isladogs · Answer

Glad you have a working solution

Forum Discussion

Real example for Total:Expression in query

5 Replies

Resources