By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,867 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Access SQL query help

P: n/a
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,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

Apr 26 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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=Station.StationName
AND Station.StationName 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=qrySomeQuery.FieldY
WHERE...

Hope this helps.

Apr 26 '06 #2

P: n/a
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.yourSubformName.Form.Requery

Replace "yourSubformName" 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 ***
Apr 26 '06 #3

P: n/a
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)="KEY0"))
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.MaxOfValue) AND (db.Station = Query1.Station)
WHERE (((db.Name)="key0"));

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","key3")

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

Apr 26 '06 #4

P: n/a
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

Apr 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.