help with not so simple query

I apologize for reposting my question. It's actually a
follow-up question, which seems more complicated than the
original.

Once more, for the sake of simplification, let's say I
have a table with 4 fields: site, measurement date,
measurement type, and measurement result:

site / date / type / result
A / 95 / X / 1.0
A / 95 / Y / 1.2
A / 96 / Y / 1.3
B / 95 / X / 1.1
B / 95 / Y / 1.4
B / 96 / Y / 1.3

and so on... What I am trying to do is extract the
maximum result for each site/type combinantion and
extract the most recent result for each site/type
combination. So extracting the maximum results for the
example table above would give me something like

site / date / type / max_res
A / 95 / X / 1.0
A / 96 / Y / 1.3
B / 95 / X / 1.1
B / 96 / Y / 1.3

And extracting the most recent results would yield:

site / date / type / rec_res
A / 95 / X / 1.0
A / 96 / Y / 1.3
B / 95 / X / 1.1
B / 95 / Y / 1.4

This has been driving me crazy. My SQL knowledge is
somewhat poor, and I am pretty sure I need to do this in
SQL.

Thank you in advance!

Here's my guess as to your problem

When you use "Group By" in a query, the records in the
resultant recordset do not correspond to any particular
record in the source recordset. In other words, you are
not "picking" a row from the source to be included in the
result.

When you show your "wanted" result for Maximum Results for
a site/type combination, you can only return those three
columns. You have included the "Date" column in your
expected results

So your first query should be
Select Site, Type, Max(Result) from yourtable group by
site, type;

If you need to see the date, you could include a fourth
column, but it would be like
Max(date), min(date), first, last etc. and there is no
guarantee that the date and the result in the query output
will be from the same record.

Likewise in your "most recent" query, you cannot show the
result.

So your second query should be
Select Site, Type, Max(Date) from yourtable group by site,
type;

If you really need to see what the result was for each of
the records returned in the second query, create a third
query based on the second query and the original record
source
like

Select Site, Type, Date, result from yourtable inner join
SecondQuery on
yourtable.Site = SecondQuery.site
and yourtable.type = SecondQuery.type
and yourtable.date = SecondQuery.MaxOfdate;

and don't be surprised if you get multiple records for
each site/type/date.


-----Original Message-----
I apologize for reposting my question. It's actually a
follow-up question, which seems more complicated than the
original.

Once more, for the sake of simplification, let's say I
have a table with 4 fields: site, measurement date,
measurement type, and measurement result:

site / date / type / result
A / 95 / X / 1.0
A / 95 / Y / 1.2
A / 96 / Y / 1.3
B / 95 / X / 1.1
B / 95 / Y / 1.4
B / 96 / Y / 1.3

and so on... What I am trying to do is extract the
maximum result for each site/type combinantion and
extract the most recent result for each site/type
combination. So extracting the maximum results for the
example table above would give me something like

site / date / type / max_res
A / 95 / X / 1.0
A / 96 / Y / 1.3
B / 95 / X / 1.1
B / 96 / Y / 1.3

And extracting the most recent results would yield:

site / date / type / rec_res
A / 95 / X / 1.0
A / 96 / Y / 1.3
B / 95 / X / 1.1
B / 95 / Y / 1.4

This has been driving me crazy. My SQL knowledge is
somewhat poor, and I am pretty sure I need to do this in
SQL.

Thank you in advance!

.