If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
This query not giving correct results
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 |
#2
|
|||
|
|||
This query not giving correct results
Bob H wrote:
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 |
#3
|
|||
|
|||
This query not giving correct results
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 wrote: 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 |
#4
|
|||
|
|||
This query not giving correct results
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 wrote: 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 wrote: 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 |
#5
|
|||
|
|||
This query not giving correct results
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 Bob H wrote: 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 wrote: 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 wrote: 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 |
Thread Tools | |
Display Modes | |
|
|