Top 1 ?

[Mike]

I am trying to construct a query that gives me the latest
and the earliest meter reading for equipment from an
equipment history table.

The attributes that I want are EQUIPNUM (ID given to each
piece of equipment), METER (a recording of the equipments
hour meter), DATE (the date of the meter reading).

The query that I am trying (but isn't working) is as
follows:
SELECT TOP 1 EQUIPMENT_HISTORY.
[EQUIPNUM],EQUIPMENT_HISTORY.[METER] AS Latest,
EQUIPMENT_HISTORY.[DATE]
FROM EQUIPMENT_HISTORY
WHERE (((EQUIPMENT_HISTORY.DATE)<=[Enter Ending Date]))
GROUP BY EQUIPMENT_HISTORY.[EQUIPNUM], EQUIPMENT_HISTORY.
[METER],EQUIPMENT_HISTORY.[DATE]
ORDER BY EQUIPMENT_HISTORY.[DATE];

If someone can tell me what I'm doing wrong, I would
appreciate it. I've been playing with this for days and
cannot get it to work correctly.
Thanks,
Mike

 

[Brian Camire]

For the latest meter reading, you might try a query whose SQL looks like
this:

SELECT
EQUIPMENT_HISTORY.EQUIPNUM,
EQUIPMENT_HISTORY.METER,
EQUIPMENT_HISTORY.DATE
FROM
EQUIPMENT_HISTORY
WHERE
EQUIPMENT_HISTORY.DATE =
(SELECT
MAX(SELF.DATE)
FROM
EQUIPMENT_HISTORY AS SELF
WHERE
SELF.EQUIPNUM = EQUIPMENT_HISTORY.EQUIPNUM
AND
SELF.DATE <= [Enter Ending Date])

For the earliest reading, change the MAX to MIN.

For a discussion of this and other approaches, you might refer to:

http://www.mvps.org/access/queries/qry0020.htm