Summarising daily rainfall data (1 Viewer)

Berlinomatt

New member
Local time
Tomorrow, 02:27
Joined
Aug 10, 2020
Messages
14
Hi,

I apologise again if this is something I should rather being doing in Power Pivot but I thought it might actually be better in Access. I have timeseries data with recordings of rainfall every 5 minutes for multiple locations all in one table. I'm trying to create a query that will sum total daily values for rainfall for each location. As an add on if possible I would like to exclude days if more than 50% of their values have the value '-9999' which meant no data was recorded. This is an example of the data:
Access.JPG


I did search the forum for 'summarise daily values' but was unable to locate something similar (which I'm sure there is I just could'nt find it).

My second question is whether there is a simple method to locate gaps in the data (instances where no recordings were made). These can sometimes be months long and sometimes only 10 or 15 minutes long. As the data set has over 2 million rows I can't simply identify blanks and then go through it as it would take an eternity. Unfortunately I also do not know SQL yet so if you have a method it would be really helpful if its shown using the 'normal' query or report design layout.

1.JPG


Many many thanks in advance,

Matt
 

Minty

AWF VIP
Local time
Today, 23:27
Joined
Jul 26, 2013
Messages
8,909
Something like the following should work;

SELECT Location, DateValue([Time]) as DayDate, Sum(Precip) FROM YourTable WHERE Precip <> -9999
GROUP BY Location , DateValue([Time])

Note that Time is a reserved word and will need the [ ] around it.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:27
Joined
Feb 28, 2001
Messages
21,175
The problem of wanting to exclude dates where you have less than a certain number of readings leads me to questions about what you want to do next. SQL can help you and will make this operation run faster than any other programming method you might choose.

Is this information stored in Access tables or Excel tables? With that many rows and your comments on "Power Pivot" I would have to think it is stored as a spreadsheet. I am going to try to answer this question based on that assumption. If I am wrong, please forgive me for the incorrect assumption.

First, you would link this information as a linked table in Access. If you did, the column headings become usable as field names based on how you did the linking.

Next, build this query, which is similar but not identical to what Minty showed you:

QueryA:
Code:
SELECT Location, DateValue([Time]) As PDate, IIF( Precip=-9999, 0, Precip) as AdjPrecip, IIF( Precip=-9999, 1, 0) As PFlag
FROM TableName
GROUP BY Location, DateValue([Time]) ;

This query should then give you what you wanted to see:

Code:
SELECT Location, PDate, Sum(AdjPrecip) as TotalPrecip, Count(Pflag) as GoodSamples
FROM QueryA
HAVING GoodSamples > {whatever is 50% of your daily sample count} ;

You might have to play with it a little bit, but I think this might be close to what you really wanted.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:27
Joined
Feb 19, 2013
Messages
13,566
Year and Month are also reserved words

will ALL reserved words be accepted by any transactional execution if enclose in [ ] symbols?
usually, but I have seen exceptions over the years, perhaps 20 or 30 -and even when the name includes other separate words such as [birth date]. And usually to do with some sort of calculation. Lose the space and it works. Unfortunately what works in one query, won't in another.

I did start this thread a couple of years ago to try and gather information on these exceptions, but didn't go anywhere

 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:27
Joined
May 7, 2009
Messages
14,607
on your 1 question.
your Requirement:
As an add on if possible I would like to exclude days if more than 50% of their values have the value '-9999' which meant no data was recorded.
create first a query (Query1) that will sum all precip by location, date and count the number of records WHERE precip > -1:
Code:
SELECT yourTable.location,
DateValue([time]) AS [Date],
Sum(yourTable.precip) AS SumOfprecip,
Count(1) AS Expr1
FROM yourTable
WHERE (((yourTable.precip)>-1))
GROUP BY yourTable.location, DateValue([time]);
next, create a second query (query2) that will count how many -9999, group by location and date:
Code:
SELECT yourTable.location,
DateValue([time]) AS [Date],
Count(1) AS Expr1
FROM yourTable
WHERE (((yourTable.precip)=-9999))
GROUP BY yourTable.location, DateValue([time]);
combine the 2 queries to filter only those whose precip count (not -9999) is more than 50%:
Code:
SELECT Query1.location, Query1.Date, Query1.SumOfprecip
FROM Query1 INNER JOIN Query2
ON (Query1.location = Query2.location) AND (Query1.Date = Query2.Date)
WHERE ((([Query1].[Expr1]/([Query1].[Expr1]+[Query2].[Expr1]))>0.5));
 

Berlinomatt

New member
Local time
Tomorrow, 02:27
Joined
Aug 10, 2020
Messages
14
Thank you all for your responses and I will try them one buy one and see how it goes. Like I said I'm just starting out with SQL so you will have to forgive the many stupid questions. I'm trying to complete youtube tutorials but still its a slow process.

@arnelgp I think the first two queries worked fine but in the third for some reason some data appears to have been excluded.

This is the code used for query 1 and the results:

Code:
SELECT Weather.location, DateValue([time]) AS [Date], Sum(Weather.precip) AS SumOfprecip, Count(1) AS Expr1
FROM Weather
WHERE (((Weather.precip)>-1))
GROUP BY Weather.location, DateValue([time]);
Query1.JPG


And from query 2:
Code:
SELECT Weather.location, DateValue([time]) AS [Date], Count(1) AS Expr1
FROM Weather
WHERE (((Weather.precip)=-9999))
GROUP BY Weather.location, DateValue([time]);
Query2.JPG


And then by query 3 for example it excluded rainfall data from 01/05/2020 from Kibale where there were no -9999 values so they should have been included but maybe my code was wrong:
Code:
SELECT Query1.location, Query1.Date, Query1.SumOfprecip
FROM Query1 INNER JOIN Query2 ON (Query1.location = Query2.location) AND (Query1.Date = Query2.Date)
WHERE ((([Query1].[Expr1]/([Query1].[Expr1]+[Query2].[Expr1]))>0.5));
Query3.JPG


There are further columns that may have -9999 values but I don't think it is making the calculation based on the total number of -9999's per row as you specified 'precip' in your code. Just so you can see this is what the whole table looks like:

1st May.JPG


Could it be excluding the rainfall data from the 01/05/2020 because there are more than half the values that have zeros?

Many thanks once again.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 23:27
Joined
Feb 19, 2013
Messages
13,566
Your inner join needs to be a left join. If there are no matching dates, you still need to see the values in query1
 

Berlinomatt

New member
Local time
Tomorrow, 02:27
Joined
Aug 10, 2020
Messages
14
Changing to 'LEFT JOIN' gives the same data:
Code:
SELECT Query1.location, Query1.Date, Query1.SumOfprecip
FROM Query1 LEFT JOIN Query2 ON (Query1.location = Query2.location) AND (Query1.Date = Query2.Date)
WHERE ((([Query1].[Expr1]/([Query1].[Expr1]+[Query2].[Expr1]))>0.5));

Left join.JPG
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:27
Joined
May 7, 2009
Messages
14,607
add Nz() function since there is no data for -9999 for that date:
Code:
SELECT Query1.location, Query1.Date, Query1.SumOfprecip
FROM Query1 LEFT JOIN Query2 ON (Query1.location = Query2.location) AND (Query1.Date = Query2.Date)
WHERE ((([Query1].[Expr1]/([Query1].[Expr1]+Nz([Query2].[Expr1], 0)))>0.5));
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:27
Joined
Feb 19, 2013
Messages
13,566
doesn't look the same to me. in your post #7, query3 does not include any data for May, in post #9, it does
 

Berlinomatt

New member
Local time
Tomorrow, 02:27
Joined
Aug 10, 2020
Messages
14
@CJ_London I was checking a specific period in May 2020 as a reference and you can see in post #7 and post #9 both have Kibale 25/05/2020 but neither have the data for the 01/05/2020 which you can see in the raw data

@arnelgp

You my friend are an absolute legend!

Query 3 amended.JPG


If I want to increase the accuracy i.e. instead of including any day with more than 50% of the values to say 90%, do I simply need to modify the last piece of code from 0.5 to 0.9?

Many thanks,

Matt
 

Users who are viewing this thread

Top Bottom