aggregate query question

[Guest]

I have a user who has a single data table that tracks observations.

The fields are:
CF - what is being observed
OD - Observation Date
EE - Employee observing
Desc - Description field
Value - Observation results

She wants to run a query that returns the last date of an observation for
each CF. There are multiple CF's with multiple OD's.

I have run a query for the MAX of OD, grouped by CF where DESC is a certain
value. If I run that, everything is fine. When we add EE and Value to the
query, we're forced to choose an aggregate for those fields as well and we
get more results than what is desired. I have tried running 2 separate
queries with no luck also. I don't have much exp. with Access, any
suggestions?

 

[Marshall Barton]

I have a user who has a single data table that tracks observations.

The fields are:
CF - what is being observed
OD - Observation Date
EE - Employee observing
Desc - Description field
Value - Observation results

She wants to run a query that returns the last date of an observation for
each CF. There are multiple CF's with multiple OD's.

I have run a query for the MAX of OD, grouped by CF where DESC is a certain
value. If I run that, everything is fine. When we add EE and Value to the
query, we're forced to choose an aggregate for those fields as well and we
get more results than what is desired.

What do you want the query to return for EE when there is
more than one possibility?

I suspect that what you want is the EE, Desc and Value for
the records with the latest OD for each CF?? If so, try
using two queries, first, create a query to get the max date
for each CF:
SELECT CF As XCF, Max(OD) As Latest
FROM thetable
GROUP BY CF

Then join that query to the table on the two fields, CF and
OD:
SELECT CF, OD, EE, Desc, Value
FROM thetable INNER JOIN firstquery
ON CF = XCF And OD = Latest

 

[Guest]

Thanks, I wasn't doing the join correctly when doing multiple queries.