How do I select the maximum of a subset of records?

[Rachel Garrett]

I'm trying to put together a tracking database for an internal
assessment system. Here's where I'm stuck:

Each question [Question name] we're assessing has scores [Score]
associated with assessment dates [Date]. Since scores can go up and
down, I want to find the score that's associated with the most recent
assessment date. However, I can't even get to the most recent
assessment date.

The data looks like this…

Question name Date Score
9.1 11/2/07 3
9.2 11/3/07 4
9.1 3/1/08 2
1.1 11/2/07 3
1.1
1/28/08 2


What I want to get back, as a query result, is this...

Question name Date Score
9.1
3/1/08 2
9.2
11/3/07 4
1.1
1/28/08 2

I want to put an expression in "Most recent assessment" that will look
at all the records where

.[Question name] = 9.1, and return the
MAX Date of those results. But I can only do this:

SELECT MAX([Table.Date]) AS 'Most recent assessment'

When I try to say SELECT MAX ([whatever]) WHERE ([some condition]),
Access burps. Is there a way to select the maximum of a *subset* of
the records, rather than having to do maximum on the whole column?

Thank you.

--Rachel Garrett

 

[Rachel Garrett]

Marsh,

Thank you. I'm confused about what the T and the X are. When I type it
in as written (substituting my [table name] for the word "table" of
course), I get prompted for parameters for T and X. What am I supposed
to substitute for those?

Thanks,
Rachel

 

[Rachel Garrett]

Marsh,

Thank you. I'm confused about what theT andthe X are. When I type it
in as written (substituting my [table name] for the word "table" of
course), I get prompted for parameters forT andX. What am I supposed
to substitute for those?

Thanks,
Rachel

If this helps...here is what my SQL looks like:

SELECT T.*
FROM [Milestone Meetings] As T
WHERE [T].[Assessment Date] = (SELECT Max([X].[Assessment Date])

FROM [Milestone Meetings] As X

WHERE [X].[Question Milestone] = [T].[Question Milestone]) ;

"Question Milestone" is the field containing the question name. I get
prompted for parameters for X.Question Milestone and T.Question
Milestone.

Thanks,
Rachel

 

[Rachel Garrett]

Never mind--it works exactly as you had it. I had added "FK" to the
Question Milestone name, as a reminder to myself that it was a foreign
key.

Thanks Marsh!