qry with select statement expression problem 1

[TrollBro]

I'm hoping someone can help me.

I have a qry with two tables: tblParent (100 records, 5 fields) and tblChild (300 records, 8 fields). I am trying to produce a query that generates a list of the 100 parent records with a 6th parent field that represents the child region value having the highest score.

in other words,
1 Parent record has three child records with score and region values:
North, 60
South, 24
East, 92

I want to have "East" as the result for this record when I run the qry. I can creat and link separate qrueries to do it, but I'm preferring to have it all self contained in one qry.

Is there a way to do this with an expression using a select statement?

Thank you

 

[dhookom]

You can create a query with a subquery like:

SELECT P.*, C.S
FROM Parent P JOIN Child C on P.PK = C.FK
WHERE C.Score = 
(SELECT Max(SCORE)
 FROM Child C2
 WHERE C2.FK = P.PK)

Duane
Hook'D on Access
MS Access MVP

 

[TrollBro]

dhookom - Thanks! this was exactly what I was looking for.

 

[PHV]

Another way, without subquery but an embedded view:

SELECT P.*, C.Region
FROM (Parent P
INNER JOIN Child C ON P.PK = C.FK)
INNER JOIN (SELECT FK,Max(Score) AS MaxScore FROM Child GROUP BY FK
) M ON C.FK = M.FK AND C.Score = M.MaxScore

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