Not seeing any way to do this with a query alone. Here is one way to accomplish. Import the text file to an Access table. Add a field TimePeriod30. Run this VBA code to populate the new field (be sure to include 'Microsoft ActiveX Data Objects 2.8 Library' in the VBA references):
Code:
Public Sub Time30()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
Dim siteID As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM SppSite;", cn, adOpenForwardOnly, adLockPessimistic
siteID = rs!site
While Not rs.EOF
If siteID <> rs!site Then
i = 0
siteID = rs!site
End If
If rs!Time < i * 30 Then
rs!TimePeriod30 = i
rs.MoveNext
Else
i = i + 1
End If
Wend
rs.Close
End Sub
Query the table to summarize data.
SELECT Site, TimePeriod30, Val(nz(DMax("SpCnt","SppSite","Site=" & [Site] & " And " & " TimePeriod30=" & [timeperiod30])-DMax("SpCnt","SppSite","Site=" & [Site] & " And " & " TimePeriod30=" & [timeperiod30]-1),DMax("SpCnt","SppSite","Site=" & [Site] & " And " & " TimePeriod30=" & [timeperiod30]))) AS SpeciesTotal
FROM SppSite GROUP BY Site, TimePeriod30;
Since the SpCnt field is cumulative for each site, don't think can just count or sum the values. To get the additional species counted in each period must subtract the previous period. Unless I misunderstand your requirement and you do want to show cumulation through the periods. Adjust the query to suit.