Problem with Aggregate query

[Guest]

Hi all

I have a table representing meter readings for utilities supplies. The table
has only four fields as follows:

Meter Reading ID: Autonumber Primary Key
Meter ID: ID number for the physical meter itself
Date Of Reading
Meter Reading: The actual value of the reading

I want to create a query which returns the date and reading for the most
recent meter reading, grouped by Meter ID.

I create an aggregate query with Group By on the Meter ID field and Max on
the Date Of Reading field. How do I include the Meter Reading field? If I add
it as Group By the query now returns more than one meter reading per Meter
ID! I just want it to return the Meter Reading value corresponding to the
most recent meter reading!

Thanks in advance

David

 

[John Spencer]

You need a query that looks something like the one below.

SELECT *
FROM [Utilities Table]
WHERE [Date Of Reading] =
(SELECT Max([Date of Reading])
FROM [Utilities Table] as Temp
WHERE Temp.[Meter Id] = [Utilities Table].[Meter ID])

Or you need TWO queries.

Query one you already created. Which is an aggregate query showing just the
Meter Id (Group by) and Date of Reading (Max). Save that query
(qLastReadingDate)

Open a new query window and add in the utilities table and the saved query
(qLastReadingDate)
Join Meter Id to Meter Id and Date of Reading to MaxofDate_Of_Reading.

Add the fields you want to see to the grid.