This query not giving correct results

[Bob H]

I am trying to find the date when we had the most rainfall out of 3234
records, so with the first query to get the maximum rainfall in a month
I get 110 records with one null and one '0' value.
So this query is saying that out of 3234 records there has only been 108
days when we had rain. We probaly had more than that in one year never
mind in 10 years.
This cannot be right because we had 24 days of rain in November 2009 but
the query only shows 16 for that month!. So how does it actually work?

SQL for this below:

SELECT Max(tblWXDataLeeds.ReadingDate) AS MaxOfReadingDate,
tblWXDataLeeds.RainFall
FROM tblWXDataLeeds
GROUP BY tblWXDataLeeds.RainFall
HAVING (((tblWXDataLeeds.RainFall)>0))
ORDER BY Max(tblWXDataLeeds.ReadingDate);

Thanks

 

[Bob H]

I am trying to find the date when we had the most rainfall out of 3234
records, so with the first query to get the maximum rainfall in a month
I get 110 records with one null and one '0' value.
So this query is saying that out of 3234 records there has only been 108
days when we had rain. We probaly had more than that in one year never
mind in 10 years.
This cannot be right because we had 24 days of rain in November 2009 but
the query only shows 16 for that month!. So how does it actually work?

SQL for this below:

SELECT Max(tblWXDataLeeds.ReadingDate) AS MaxOfReadingDate,
tblWXDataLeeds.RainFall
FROM tblWXDataLeeds
GROUP BY tblWXDataLeeds.RainFall
HAVING (((tblWXDataLeeds.RainFall)>0))
ORDER BY Max(tblWXDataLeeds.ReadingDate);

Thanks

If I don't groupby Max, then the query returns over 1000 records, but
groupby Max reduces that to just over 100!!

Confused

 

[John Spencer]

The following should show you the date (or Dates) with the maximum rainfall
out of the 3234 records.

SELECT ReadingDate, Rainfall
FROM tblWXDataLeeds
WHERE tblWxDataLeeds.Rainfall =
(SELECT MAX(RainFall)
FROM tblWxDataLeeds as Temp)

If you want the maximum rainfall and date for each year, each month, each
month of each year, etc. then tell us what you want.

For instance, to get the maximum rainfall date in each year.
SELECT ReadingDate, Rainfall
FROM tblWXDataLeeds
WHERE tblWxDataLeeds.Rainfall =
(SELECT MAX(RainFall)
FROM tblWxDataLeeds as Temp
WHERE Year(Temp.ReadingDate)= Year(tblWxDataLeeds.ReadingDate))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob H]

Thank you for your help here.
The first SQL statement gives me the max overall rainfall by date, and
the second gives me the max of all the years by date, but it causes
access 2007 to be really slow to unresponsive while it thinks about
getting the records. That is something I can live with for now.

Thanks again

 

[John Spencer]

It should not be all that slow with that limited number (3234) of records.
Make sure you have indexes on both your fields.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County