Access Textbox control source outside current report

Question

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.

Answer 1

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"