I am creating a Report in MS Access 2016. I want textboxes showing a summary of data from other tables - without actually showing rows of those tables (no subreports if possible). For example, I have tried to create a textbox with formula
=Format(Avg([WeekData].[DIFOT]),"##0.0%") & " DIFOT This Week"
which should return something like
100% DIFOT This Week
(NB Weekdata is a query and DIFOT is a field in that query, all in the same database as this report.)
However instead it just gives me #Error.
Please can you tell me the most efficient way to pull together summary figures such as these without creating any more queries and/or subreports than absolutely necessary? I'm quite new to SQL and Access on the whole.
Many thanks in advance.
Use DAvg() domain aggregate function. Also, the ## characters in Format() pattern serve no purpose.
=Format(DAvg("DIFOT", "WeekData"), "0.0%") & " DIFOT This Week"
or
=Format(DAvg("DIFOT", "WeekData"), "Percent") & " DIFOT This Week"