MS Access: average subset of records on report -

i have database of test results tables students, tests , test_results. each record in test_results refers key students, key tests, score , yes/no field absent. have report subheadings (grouped by) each test. want display average score each test on test's heading want calculate average on records student not absent.

i unsuccessfully tried 2 approaches:

1. a query named averagescorewherenotabsent select avg(score) expr1 test_results (((test_results.absent])=false));; , unbound field on report's test grouping subheader, source expression =dlookup("[score]","[averagescorewherenotabsent]![expr1]","[test] = " & [id]). unfortunately returns '#error'. tried tweaking ask me parameters or - oddly - display score of last student instead of average. what's wrong syntax?!

2. some vba loops through test_results looking fields match current test id , not marked absent. sums , averages them , updates unbound field on test subheader. code called when report loads. worked...but each test showed same average. problem obvious :( in report view, field repeated every time subheader repeated i.e. each test, in design view see 1 subheader , 1 field - don't know way refer each subheader , field individually vba - suppose it's same field!

i assume answer trivial i'm new access - missing? thanks!

if want average test, add test group variable in aggregate query (of course adjust id actual field name indicates test):

select test_id, avg(score) averagescore test_results  (((test_results.[absent])=false)) group test_id; 

then dlookup on report following (of course adjust report names , test_id control actual names , sure control exists in section placed):

=dlookup("[averagescore]","[averagescorewherenotabsent]",          "[test_id] = " & reports!reportname!test_idfield)