Query Trouble

[MLT]

I've got a table with the following columns: TagID, Date,
Measurement. So for each TagID, there are multiple Times and
associated Measurements listed. What I'm trying to do is find the
minimum Measurement for each TagID, but also find the Date associated
with that minimum Measurement. I can find the minumum measurement
pretty easily by doing a query and using Group By for TagID and Min
for Measurement. The trouble comes in when I want to also know the
Date associated with that minimum Measurement. Does anyone know how
to setup a query that would result in a table with each TagID listed
with its own Date and min Measurement??? thanks for any help!!

 

[MGFoster]

I've got a table with the following columns: TagID, Date,
Measurement. So for each TagID, there are multiple Times and
associated Measurements listed. What I'm trying to do is find the
minimum Measurement for each TagID, but also find the Date associated
with that minimum Measurement. I can find the minumum measurement
pretty easily by doing a query and using Group By for TagID and Min
for Measurement. The trouble comes in when I want to also know the
Date associated with that minimum Measurement. Does anyone know how
to setup a query that would result in a table with each TagID listed
with its own Date and min Measurement??? thanks for any help!!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Like this:

SELECT TagID, [Date], Measurement
FROM table_name As T1
WHERE Measurement = (SELECT MIN(Measurement) FROM table_name
WHERE TagID = T1.TagID)

This query assumes that the Measurement is only made once per date.

For good table design I suggest you change the name of [Date] to a more
descriptive name like "measurement_date." Because Date() is a VBA
function and could be ambiguous to anyone who may come after you.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSk41o4echKqOuFEgEQJNPACguxP1k/0Wk01Bxi+oHFbwnDZOO88AoLX8
wkHJgNNAllVE3Qq4A4QAev6/
=Agd6
-----END PGP SIGNATURE-----