Help with Weather Station Data

[RainMan]

Hi, I'm hoping I can find some help on here. I don't really know much about Access, queries, etc. I've been tasked with managing several personal weather stations in my area for my job. Every month I go out and download the data from each weather station to my laptop.



The data from the weather station software exports as a .txt file, which I import into excel. Then I make some adjustments to the spreadsheet and upload it into an Access Database.

If you look at the picture below, noting the date and time, the weather station takes a reading every 15 minutes. My data goes back to almost two years ago.

The station records a ton of different weather variables - what is shown in my picture below is about 1/4 of the entire spreadsheet. Is there a way to quickly compile the data taken every 15 minutes into a daily total? daily average? etc.? Keyword = quickly!

I would like to be able to produce tables and graphs that can show daily, weekly, monthly, and annual totals and/or averages. Would it be easier to accomplish these things in Excel?

Any help is appreciated!
-Dan

[aytee111]

This is the perfect setup for Access - the only problem is that Access has a 2gb limit and with your amount of data that will be used up quickly. You can use SQL Server which would be the best way to go, or else create different Access back-ends to store the data, maybe separated by year.

You would have a few tables, e.g. weather stations, departments, and readings. Then a simple macro or VBA function to import the textfile and add the data to a table(s). Then you would have a number of queries to display the data in any format you desire - yes, at the click of a button! This can be easily done once the decisions have been made, and we can help along the way.

[RainMan]

I appreciate the reply! I may be wrong, but I might be at the step where I'm ready to create queries to display the data that I want. Each weather station has its own Table which displays all of the variables it collects.

Can you walk me through the basic steps for something simple, like finding total daily rainfall amounts for the month of January for an individual weather station? Once I figure out how to use queries, I think I can play around with it enough to figure it out - I've never used Access for anything other than importing data to an existing database.

[aytee111]

First you must make decisions and create the database structure, before jumping in to displaying the data. What tables are you going to have, what will they look like, what will they contain? You can make this an actual Access database or you can make it a glorified Excel spreadsheet.

There are lots of youtube videos and other links where you can learn how to do queries. Come back here with specific questions.

[orange]

What exactly do you do with the data? Who else uses it?

Could you please provide a little more detail re the following:

managing several personal weather stations in my area for my job

How many? Locations? Job type??
Need for historical data?
Every 15 minutes seems quite frequent -- there must be some rationale for that.

Since you seem to be new to database and Access, I recommend you review some youtube videos regarding Intro to Database and Intro to Access, and work through some tutorials.
Here is a link that may be helpful. It is a longer thread --- a dialog where the OP was trying to design a database using trial and error.

Good luck with your project.

[RainMan]

I work for the Stormwater Regulatory Dept for my local County Govt. We use the data to monitor rainfall throughout the county, which in turn helps us focus our efforts for drainage issues and doing inspections of commercial and residential job sites. We use it but we don't rely on it. This database is more for being able to go back and look at rain events, generate monthly rainfall reports, etc. We currently have 5 stations that I download data from.

The database and tables already exist. There are several tables - each one is dedicated to an individual weather station and all the data that I download out in the field. I bring it back to the office and append the data to its respective table.

If you go back to my OP, the picture shows an example of one of my tables. I want to be able to go back to a particular weather station, choose a specific month, and be able to produce information from it. Lets say, specifically, I want to be able to look at the daily rainfall amounts for the month of April at the Gotham City weather station. Since it records data every 15 minutes, how do I tell Access to look at all of the rainfall data collected throughout that day and give me a sum? How would I tell Access to give me a sum for each individual day for a particular month? How would I ask it to give me monthly totals? etc. At this point, I'm not concerned with creating relationships between the different tables (different weather stations). I'd like to figure out how to manipulate the data on just one individual table for now.

I watched some youtube videos. The things that get me jammed up when I was trying to create queries were the 15 minute intervals in the data and also the "Date/Time" column - date and time are not seperated.

[davegri]

Could you upload a db with one of your tables with a couple of months' worth of data here?