Calc Average from selected rows

[vae]

This is a continuation of a nasty problem I have to write a query for. The count was worked out but now need the average.

In a one table I have various attributes. The 3 in question are [admission_id] , [answer], and [mark]. The [admission_id] can have the same value in different rows. (e.g the value 235 can appear 3 times).
The [answer] can have either value 'Yes" or "No'. The [mark] will have a number value.
My query needs to find the average total of the [mark] for every distinct [admission_id] from the rows which has a 'Yes' value in the [answer] .

I have to list all the Admission_id entrys even those that have "no" in the [answer] column so there will be some so a simple SELECT admission_id, average /FROM table /WHERE [answer] = "Yes' doesn't appear to work as doesn't return all admission_id entrys

{Hopefully i explained it ok}
Thanks

 

[MStef]

Look at "DemoAverageYesA2000.mdb" (attachment, zip).
Look at Table, query.
Run query.

 

[vae]

Thanks for that,
This query works well in isolation. My problem is I have to list all the [admission_id] entry's, not just the ones that match the 'Yes' value in the [answer] column. So this query returns only a partial list of the [admission_id] entrys, not all of them.