Max and min values

[dobosm1421]

I am working with some temperature data and trying to extrapolate information for a summary. I have tables that include the location of where temperature is taken, and measurements taken each hour for each station everyday for a year. I would like to extract daily maximum and minimum temperature from these data.

I tried to group by min and max temp by the date given and that didn't work. Is there an efficient way for access to calculate the max and min values from such a dataset?

 

[Alansidman]

Suggest you post your SQL statement for analysis

 

[dobosm1421]

The SQL statement for the query that I am trying to build? I have not really used the SQL to communicate my data before so I am unsure how it works and what I need to provide.

 

[Alansidman]

If you have built the query in the QBE (the aggregate one that does not do what you want, based on the premise you explained in the first thread), in the upper left hand corner there is a drop down arrow. Click it. Select SQL and it will open the window that displays the SQL statement for the query you have built in the QBE. Copy and paste it into a thread. Surround it with Code Tags for easier viewing.

 

[dobosm1421]

This is the SQL:

SELECT Date.Date, Max(Mainstem.Crooked) AS MaxOfCrooked
FROM [Date] RIGHT JOIN Mainstem ON Date.Date = Mainstem.Date
GROUP BY Date.Date;

 

[Alansidman]

I am a little confused by your query. If the date is in the Date Table and in the Mainstem table, why do you need to join the two tables since you are only trying to evaluate the Max of Crooked by Date?

BTW: As a point of interest, the word DATE is a reserved word in Access and should not be used to name fields or tables as it may cause issues in future actions. For a complete list of reserved words and symbols, google "Access Reserved Words"

 

[dobosm1421]

SELECT Mainstem.Event_date, Max(Mainstem.Crooked) AS MaxOfCrooked
FROM Mainstem
GROUP BY Mainstem.Event_date;

Here is the SQL with the stand alone table (mainstem). I was trying the extra table because I could not get it to work by sorting what I needed in the "Mainstem" table so disregard the last SQL.

There are 24 records (temperature) for each day and I am just trying to extract mean daily min and max temperatures.

 

[Brianwarnock]

Might I be correct in guessing that event_date includes a time value?
If so use Datevalue(event_date) to select only the date value, and group on that also. I believe that you can select the Min value in the same query.

Brian

 

[dobosm1421]

Yes! Thank you! I thought that I extracted the time from the date when I imported the file but it was still embedded in there. The expression worked and I did get the values I needed!