Can't quite grok a Sub-Query

[dbc23]

I know a sub-query is the solution to my problem, but I can't manage to wrap my head around how the SQL integrates into the query as built by QBE.

I have 2 tables:

CompletedDrives
DriveResults

The fields I'm referencing are:


CD.DriveID
CD.Date
CD.Account
DR.TotalResults

They're Joined on a field "DriveID"

What I need is to return the average of the 3 most recent drives by account.

*Pseudo code*
FOR Each CD.Account
Select 3 most recent DriveIDs by Date
Average the TotalResults of those 3 drives from DriveResults
Return As:
Account | AverageOf3Drives

This should be super simple from everything I've read but I'm very new to SQL and am used to using the QBE

ANY help is appreciated,
Aaron

[ranman256]

wow, shout out to Heinlein.
Note you don't loop thru data in access, that's what queries are for.

the subform query can pull all records, BUT if you bind the subform to the master form on the key field,
then the subform only shows records with the master Key.
LINK MASTER FIELDS
LINK CHILD FIELDS

For most recent items, you need 2 queries:
Q1 get the largest: select top 3 ID, Max(Date) from table.
Q2 get all records: join table to Q1 on ID and get all the data.

[ssanfu]

You could use two queries as suggested by ranman.

Or you could use a sub query. See "Subquery basics" by Allen Browne