SQL Query PLZ help!!!

[Techsavy]

Hello,

I am trying to analyze 56 years of hourly rainfall data.

Right now i have three columns in access database. Date(mm/dd/yyyy), hr(0-23) and hourly Rainfall(inches).

I would like to know the count of rainfall events that exceed 2.4 inches in 24 hrs(this means the sum of rainfall in 24 hrs should be greater than 2.4 inches to be identified as an event).

once an event is identified the next event should be identified with a gap of 72 hours.

Any suggestions on how to query this?

Thanks,

 

[Kiwiman]

I had a similar problem regarding trying to identify a customer who returned within 4 days. I couldn't work out how to do it withiout creating a temp table, so I did the following (i'll tailor it for your stiuation).

I would be interested in alternatives...

Create a new table with fields :Ldate (datetime),Rainfall (number - Double), NewEvent (yes/no)

Add to this table the days where your rainfall exceeds 2.4 inches

INSERT INTO YourNewTable
SELECT 
FROM YourExistTable
GROUP BY YourExistTable.YourDate
HAVING (((Sum(YourExistTable.YourField))>2.4))

This will give you all days where the rainfall exceeded 2.4 inches.

Then I looped through this recordset to identify which dates were within my criteria - in your case 72 hours (3 days) apart. I read this as an event had to be at least 3 days from the last event??

Private Sub cmdEvents_Click()

Dim rs As DAO.Recordset
Dim strSQl As String, strSQL2 As String
Dim i As Integer, intDay As Integer, intMonth As Integer, intYear As Integer
Dim x As Boolean
Dim dteDate As Date

'What the recordset is
strSQl = "Select T1.Ldate from tblRainfall t1 order by T1.Ldate"

Set rs = CurrentDb.OpenRecordset(strSQl)

rs.MoveLast
rs.MoveFirst
'Stop
DoCmd.SetWarnings False

For i = 1 To rs.RecordCount
    'The first record will always be the first event
    'so this will always have a value of true
    If i = 1 Then
        x = True
    Else
        'Add 3 days to the previos recordset date and compare to the current recordset date
        'If the new date > the current date then not a new event
        'Otherwise it is a new event
        If DateSerial(intYear, intMonth, intDay + 3) > rs!ldate Then
            x = False
        Else
            x = True
        End If
    End If
    
    'Create the SQL to update the "NewEvents" field of the new table
    strSQL2 = "Update tblrainfall set tblrainfall.NewEvent = " & x
    strSQL2 = strSQL2 & " Where tblrainfall.ldate = #" & rs!ldate & "#"
    
    DoCmd.RunSQL (strSQL2)
        
    'I have trouble with dates, so I do it this way... I will get there eventually
    dteDate = rs!ldate
    intDay = Day(dteDate)
    intMonth = Month(dteDate)
    intYear = Year(dteDate)
    
    
    rs.MoveNext
Next i
DoCmd.SetWarnings True
       
rs.Close

Set rs = Nothing

End Sub

 

[Techsavy]

Hello,


Thanks a lot! I understood the first part where it gives me all the days that exceed a rainfall of 2.4 inches.

So the second part is a VBA code that has to be run through access VBA editor?

Please let me know,

Thanks,

 

[Techsavy]

Will the following code be an alternative to the VBA code that you mentioned?

select t1.d1

from (Select date as d1,
from table name
group by date-field
having sum(rainfall) >2.4) as t1,
from (Select date as d2,
from table name
group by date-field
having sum(rainfall) >2.4) as t2

where t1.d1 <> t2.d2 and dateadd(day,3,t1.d1)=t1.d2

 

[Kiwiman]

Howzit

So the second part is a VBA code that has to be run through access VBA editor?

Yes this is run in vba. You can create an unbound form, with a command button, on it, with a name cmdEvents. Post the code in the On Click event... That should work.

In regards to your second post, I am getting a syntax error, there are too many From stmts...