Access SQL query help

[Jim]

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,v alue,
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)=(SE LECT 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)='KEY 0')) 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

[pietlinden]

Jim,

are all the Station values in a table somewhere? If so (or you could
put them into one), then you could just join to the filtered table. A
really simple example would be:

DB.STATION, DB.NAME, DB.VALUE, DB.TIME
FROM DB inner join tblStation ON DB.Station=Stat ion.StationName
AND Station.Station Name IN (...some subselect query here)

That way you could pretty easily filter for just the records you want
by building the list of stations you wanted (with a query) and then
including that in your second query.

eg
SELECT DB.*
FROM DB INNER JOIN qrySomeQuery ON DB.FieldX=qrySo meQuery.FieldY
WHERE...

Hope this helps.

[Rich P]

I assume you are viewing the query results in a form. What you can do
is to make that a subform and place it in another form which would be
your main form (or whatever name you want). In the main form you can
have a combobox with a list of the stations. Then, in the query in the
criteria section of the stations field you can make a reference to the
combobox on the main form

field: Station
criteria: Forms!mainFrom! combo1

Then when you select a station from the combobox on the form, the query
will query on that station. You will need to add a teeny bit of code to
the combobox. Go to the Form design to the Combobox Properties sheet.
Scroll down to toward the bottom of the property sheet for the combobox.
When you see a listing that says "After Update" go to the right of that
and click on the elipse ... button. Select "Code". Here you will write
the following:

Me.yourSubformN ame.Form.Requer y

Replace "yourSubformNam e" with the name of the form where you view your
query results (make sure that form is in datasheet view) which is now
your subform. Now you have a more dynamic query.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[chris.nebinger]

Yeah, this was tricky. I broke it down into steps:

First, find the highest value of Key0 for each station:

SELECT db.Station, Max(db.Value) AS MaxOfValue
FROM db
WHERE (((db.Name)="KE Y0"))
GROUP BY db.Station;

Save this one as Query1
Now, find the time that happened:

SELECT db.Station, db.Time, db.Name
FROM db INNER JOIN Query1
ON (db.Value = Query1.MaxOfVal ue) AND (db.Station = Query1.Station)
WHERE (((db.Name)="ke y0"));

Save this one as Query2

Finally, get all the keys for that time:

SELECT db.Station, db.Name, db.Value, db.Time
FROM db INNER JOIN
Query2 ON Query2.Time = db.Time AND db.Station = Query2.Station
WHERE db.Name in ("Key0","key1", "key2","key 3")

This Should work, but no promises. Let me know if you need more help.
Chris Nebinger

[Jim]

All,

Thanks... This gave me a new direction to go in designing my query.
My version of
ACCESS does not like using the JOIN syntax. I'm using the "from db,
query1" syntax.
Is there a difference between the two methods?

Chris, I was able to get steps 1 and 2 working with only a slight
modification for my needs. However, the final step is killing me....
I'm getting an error message of being out ot temporary
disk space. My DB is almost 2GB with 15M records! So I'm having to go
back to the drawing
board again.... :-)
Thanks,

Jim