Creating a new Database

[ACE]

Hi, I'm fairly new with Access (I've taken a few courses). I need to create a database to run a model with and am wondering what the best way to do it would be. Here is my situation. We have 150 to 200 weather stations that report data every 15 min's. I want to import this data (txt files) into Access. Next I need to take the number of instances with where certain events occur (rain and temp within a range) and run it through a model that I have in order to get an index value. I currently have this set up in Excel and it works good except Excel is just getting too small. Any suggestions on how to set this up would be greatly appreciated. My first questions about doing this is: Should I have one table that all of the data is imported into and then query out the events that I need? Or, should each station have it's own table and then do the queries?

Thanks again for the help!!!

 

[WayneRyan]

ACE,

On the surface, you should have two tables:

tblStations:
StationID - AutoNumber
StationName
Location
OtherStuff

tblEvents:
StationID (Link to tblStations)
EventType
EventData

The only thing that would cause a deviation from this is if
the types of data for events is so different from each other,
then they would have to be in separate tables.

At least this gets us started and someone else can join in and
discuss it.

Wayne

 

[ACE]

The tblStations table makes perfect sense but I have questions about the tblEvents. Eventually, I'm going to want the database to automatically update all 150 stations with the daily weather files and save all of the data from the beginning. This will run for approximately 2 months, every day. The weather events to be saved are date, temperature, relative humidity, and precipitation. How can I put this all into a database so I can access certain days in a query?

 

[Fornatian]

Presumably, the text file indicates the dataset date, if it doesn't you can use the FileDateTime function to return the creation/last modification date of the file. Including this in a recordset update loop for each file would allow you to assign this to a particular field. You could then query on that field.

Take a look here for how to loop a directory for files of a certain type.

http://www.access-programmers.co.uk/forums/showthread.php?threadid=53342&goto=newpost

alternatively, you may want to merge all the imported files together into one file and then import that, there's a link in the above for how to do that. A good way to do that is to have a linked table looking at a text file(let's call it, importrecords.txt), once the files are merged together into a single text file, rename it as importrecords.txt and voila, your database will show your new records which can then be imported into your access table.

 

[Bob the Sheep]

Okay ACE,

I work with met / air quality data, modelling and the like on a day by day basis, and have excel do some weird and wonderful things to help me sort and filter the data I use.

My thought is why not just test the data in your txt files and only port the data into excel that you need and then output a single file for your model run.

I guess you use some software to poll the data from the stations, can you not set some filters on that software.

One thing to consider that in a year you will collect over 20 million pieces of data, Access may grind to a halt trying to sift through the information.

If I can be of any further help let me know.

Hope this helps

Andy

 

[ACE]

I like the idea of importing 1 txt file that has all of the data in it, but unfortunately I can't do that. The software that I use to acquire the data won't let me do that and get hourly data. In order to get hourly or 15 min. data I have to do each station separetely. This works fine, and I linked Access to these files. The problem is that there is no way to tell these files apart other than the file name which is a unique number. I want to be able to join all of these txt files to one file that gives a description of each station. Is there any way to join files using the fieldname and filename???

Thanks!!!