Help with Group By query

CumbrianCanary

Registered User.
Local time
Today, 12:51
Joined
Sep 13, 2004
Messages
22
I have been asked by a colleague to help with a database that I have not been involved in designing.

It has data weather data for a number of times for every day. They wanted to get out the max wind speed for every day. This was easily achieved by using the Group By Day and Max wind speed in the basic query design window.

They would now like the direction associated with that speed. I can’t seem to do this easily using the standard query window. Could anyone help me please?

Thanks
CC
 
Is direction not stored into the table that your query is based off of?
 
Thanks for the reply dapfeifer, but I am not sure what you mean. The data in the tabel looks something like this:

Date, Time, Wind Speed, Wind Direction

My query groups by day and returns the maximum wind speed. However, I don't know how to return the corresponding wind direction for the maximum wind speed. I am using the totals row in the query design. I can leave this blank and don’t think that any of the drop down options would return the value I need.

Maybe I am approaching this in the wrong way. I would appreciate anyone’s thoughts on this. It seems it should be a very easy thing to do.
 
Alright, this solution should work. Add wind direction to your query. Then, right-click anywhere in the window and click properties. Make sure the window that comes up says "Query Properities." If not, click anywhere in the query window as long as it isn't one of the query columns to change it to query properties. Find the "Top Values" property, clear it, then enter the number of entries you wish to have show. If you only need the max, only have "1" (without quotes) in that box. This should display only the date, time, speed and direction of the top wind speed, as long as the direction is stored on the same line as the top speed.

If this doesn't work or I need to clarify my directions let me know.
 
Thanks dapfeifer, I have changed the Top Values in the properties and can see what it is doing but doesn’t quite do what I am after. I may have not explained things very well.

The Table looks a bit like this but contains data for an entire year.

Date, Time, Wind Speed, Wind Direction
01/08/08, 08:00, 20, 251
01/08/08, 08:15, 21, 268
01/08/08, 08:30, 80, 271

I would like to be able to extract the highest wind speed from each day. This I can do easily with the Total feature in the query design view. I set the date to “Group By” and the wind speed to “Max”. This works fine. My problem comes when I try and get the wind direction associated with the max wind speed. Using the Total system requires me to make a selection from the drop down boxes from wind direction, but none of option seems to generate the wind direction associated with the maximum wind speed.

I am beginning to think I am not approaching this problem in the correct way. I would really appreciate it if anyone has any ideas on how to solve my problem.

Many thanks,

CC
 
A second query, join your first query back to the table on day and maxofwindspeed, or whatever the field is called, to windspeed and it will select direction from the row.

Brian
 

Users who are viewing this thread

Back
Top Bottom