aggregate query question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
deo89 said:
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.
Click to expand...


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
 
Back
Top