nested query (i think)

[polkio]

Hi,

I am somewhat new to access and have a query question I am struggling with, I think the answer may be a nested query but it has so far eluded me.

I need to do the following

select records from one table based on a criteria, (i.e. select a,b,c from table1 where b = 1)

then for each record matching the criteria, select the top 5 records from another table and sum one of the fields (i.e. select top 5 d, sum(e) from table2 where f = c) - for every c in the first query.

any help much appreciated

 

[PHV]

Typed, untested:

SELECT A.a, A.b, A.c, B.f, B.d, B.SumE
FROM table1 AS A INNER JOIN (SELECT X.f, X.d, X.SumE FROM (
  SELECT f, d, Sum(e) AS SumE FROM table2 GROUP BY f, d
  ) AS X INNER JOIN (
  SELECT f, d, Sum(e) AS SumE FROM table2 GROUP BY f, d
  ) AS Y ON X.f = Y.f AND X.d = Y.d AND X.SumE <= Y.SumE
  GROUP BY X.f, X.d, X.SumE HAVING Count(*) <= 5
) AS B ON A.c = B.f
WHERE A.b = 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[polkio]

thanks very much for this, worked a treat. I was so far away from this it wasn't funny.

thanks again