If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Trouble
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!! |
#2
|
|||
|
|||
Query Trouble
MLT wrote:
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----- |
Thread Tools | |
Display Modes | |
|
|