Real example for Total:Expression in query

[JanZitniak]

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?

[#2]

@JanZitniak 

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:

[#3]

@isladogs 

Apologies. This is the screenshot of the query design

[#4]

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

[#5]

@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 :).

[#6]

Glad you have a working solution