Hi,
I have some basic SQL experience (more programming and EXCEL
experience) and I am having a hard time generating a particular query.
My table is a historical database with columns (station,name,value,
time) for each time stamp and station there may be up to 50 records
each with a different name. What I want to do is for each value of
station retrieve the values for names matching key1,key2, and key3 at
the time key0 is at it's maximum. The query below works correctly for
station JOHN, but I don't want to have to cut and paste this query for
each station. Essentially I'd like to loop through each station and
perform this same query with JOHN being the replaced by the looped
station name. I've tried several variants with no success any ideas?
SELECT DB.STATION, DB.NAME, DB.VALUE, DB.TIME
FROM DB
WHERE (((DB.TIME)=(SELECT TOP 1 DB.TIME
FROM DB
WHERE (((DB.TIME)>#11/30/2005 23:59:59#) AND
((DB.TIME)<#3/1/2006 23:59:59#) AND
((DB.STATION)='JOHN') AND
((DB.NAME)='KEY0')) ORDER BY DB.VALUE DESC)
)
AND
((DB.STATION)='JOHN' AND
(DB.NAME) LIKE 'KEY1' AND
(DB.NAME) LIKE 'KEY2' AND
(DB.NAME) LIKE 'KEY3'));
Thank you.
Jim