Learn how to Work Smarter

Showing posts with label Access. Show all posts
Showing posts with label Access. Show all posts

Wednesday, July 21, 2010

Why Database? Part 2

That example database (WhyDatabase.mdb) I was talking about -- you're going to need it today.  I'll include some screenshots of it so those without Microsoft Access can see this simple example.

Goal: store data in one place; refer to it from other places; simplify.  Here we'll just rename a grab sample from a well...

1. Open WhyDatabase.mdb.  You needn't worry about the "Security Warning" now -- that related to VBA, and proprietary MS Access goodies that we're not using (yet).

2. Make sure you're viewing All Access Objects.

3. Note that this database has four tables and one query.

We're going to open two of these objects: tblNotNormal, and qryFlatOutput.

Each object has 31 records; they contain identical data about a grab sample from MW-5 it seems.

We learned the field tech did not adhere to naming standards, so SampleName needs to be updated: "MW-5_grab" becomes "MW-5_G".  Or whatever.

In tblNotNormal -- a literal "flat file" -- this is a little more painful even than Excel -- copy/pasting each record takes ages... and there is no "rule" validating our changes for typos:

(Yes, Excel users would recognize a great opportunity for Fill Down.)

Note Access's "pencil" icon indicating which record is currently being edited.

Then give a go to qryFlatOutput -- magically simple:

Change the first record -- all the rest update when the record is "committed."

Can you misname any individual SampleName in qryFlatOutput?  Since you are updating one record, and the query points to it multiple times, the "data rule" says they all have to be names the same.

So without any of the potential "gotchas" of find-and-replace, without concern of Filling Down or other Excel unintentional error-inducing mass data crunching, you leveraged a data rule to update 31 records with a keystroke.  Strong work, friends.

Yeah, it was a little easy -- but take a look at the Relationship diagram, view design of the query, and get comfortable with what we're doing here.  Back in a couple days for more.

Friday, March 12, 2010

Keeping real-time river and precipitation data, er, current

Been dragging my heels too long on my "data normalization" piece -- was sharing hydrology data with another staff today and figured this needed written up.

One of our projects is 1) adjacent to a tidally-influenced body of water; 2) has a RCRA cap; and 3) a bunch of transducer-outfitted wells each collecting 48 records/day to prove or disprove hydraulic connection with said river.  So we track environmental parameters including daily river fluctuations and local rainfall.

Enter the Internet and web scraping.

There are a ton of different ways of pulling data from the 'Net but, frankly, the easiest to validate -- prove is working to those who ask -- is the HTTP interface (type of text handled by a browser - a webpage).  I found Excel is a fine way of scraping raw delimited text, then link that to Access and append new records where they belong.  (As municipalities improve XML interfaces I expect to go that direction -- which should make this a little more efficient!)

First -- get the river data.  The USGS provides 30-minute increment stage, flow and discharge data for the river through their website.  They have a whole page on automated data retrieval -- but with the usual caveats I worked the quick-n-dirty.


Second -- collecting precipitation data.  Our City (that works!) has a nice network of automated bucket gages hosted by the USGS.  Pulling that into Excel looks like this (right):

The pink-ish cells are the "scraped" HTML -- the cells to the right contain a little text handling, stuff like:

  • date_PST: 
    • =LEFT(A15, 11)
  • inch_precip: 
    • =IF(ISNUMBER(VALUE(B15)), VALUE(B15)*0.01, "")
  • bad_data_flag: 
    • =IF(ISNUMBER(VALUE(B15)), "FALSE", "TRUE")

Next, I used linked tables in Access to pull this into a "staged" area -- which I think is important when you're relying on outside data -- you can control and manipulate the actual data table by using SQL queries to pull from the staged data.

Here are the linked tables (Access shows them in little Excel icons), named ltblPrecip_top60 and ltblRiverGageUpdate:

Then the queries to pull data into tblPrecipitationGage and tblRiverGage look like this:
INSERT INTO tblPrecipitationGage ( LOC_ID, FILEORIG, DTT_PST, INCH_PRECIP, DATA_FLG, UpdateUser, UpdateTime, UpdateDescription )
SELECT "BES Station Number 193" AS LOC_ID, "http://or.water.usgs.gov/non-usgs/bes/astor.rain" AS FILE_ORIG, DateValue([date_measure_PST]) AS RainfallDatePST, ltblPrecip_top60.inches_rainfall, ltblPrecip_top60.bad_data_flag, fOSUserName() AS UpdateUser, Now() AS UpdateTime, "qryAppendNewPrecipitation" AS UpdateDescription
FROM ltblPrecip_top60
WHERE (((DateValue([date_measure_PST]))>(SELECT Max(tblPrecipitationGage.DTT_PST)  FROM tblPrecipitationGage) And (DateValue([date_measure_PST]))<(DateValue(Now()))));

And...:
INSERT INTO tblRiverGage ( LOC_ID, FILEORIG, LoggingDTT, TimeDatum, ELEV_NAVD88, Velocity, Discharge, Data_Flag, UpdateUser, UpdateTime, UpdateDescription )
SELECT ltblRiverGageUpdate.LOC_ID, "http://waterdata.usgs.gov/nwis/uv?14211720" AS FILE_ORIG, ltblRiverGageUpdate.loggingdtt, ltblRiverGageUpdate.TimeDatum, ltblRiverGageUpdate.Elevation_NAVD88, ltblRiverGageUpdate.Velocity, ltblRiverGageUpdate.Discharge, ltblRiverGageUpdate.bad_data_flag, fOSUserName() AS UpdateUser, Now() AS UpdateTime, "qryAppendNewWillametteRiver" AS UpdateDescription
FROM ltblRiverGageUpdate
WHERE (((ltblRiverGageUpdate.LOC_ID)=14211720) AND ((ltblRiverGageUpdate.loggingdtt)>(SELECT Max(tblRiverGage.LoggingDTT)  FROM tblRiverGage) And (ltblRiverGageUpdate.loggingdtt)<(DateValue(Now()))))
ORDER BY ltblRiverGageUpdate.loggingdtt;
Okay, those look a little hairy -- but they aren't that bad.  Really.  :-)

Gonna have to wait until tomorrow for a reasonable explanation of those -- this is a long post, and I'm out of time.  (The dog is home with her legs crossed!)

Thanks for checking this out.

Wednesday, December 9, 2009

Using strict keys

I finally modified the relationship between wells and water levels today; this was the troubling design:


...corrected to this:



Of course this made more trouble, adding and then correlating water levels with the new well screen identifiers (I just used "001" to replace all the Null values in ScreenIntervalID).  The next issue was a bit embarrassing -- my nice, clean data in tblGroundwaterLevel ... not so clean.  Found a number of violations, nulls, and weird missing data in this table.  Not surprising when accumulating data from a dozen different sources for one analytical tool, I have to constantly remind myself.

Enter the index.  This is a way of enforcing rules between records of data.  Check out UniqueKey; it's telling the tblGroundwaterLevel that records cannot repeat these four fields:
  • LocationName
  • ScreenIntervalID
  • LoggingDate
  • MeasuringMethod
Note that LoggingDate is a date/time field (as usual in Access/most DBs), so, if there are transducer readings at 8 am, 12, and 4 pm, each LoggingDate record is unique ("mm/dd/yyyy 08:00", "mm/dd/yyyy 12:00", and "mm/dd/yyyy 16:00").

Additionally, including "MeasuringMethod" guards against a key violation if there's a manual measurement at exactly the same moment as a transducer measurement is made, providing the data manager specifies different methods for the different data.


Keys guard against:
  • duplicate records
    • ex: importing from two different Excel files with overlap; or
    • importing the same transducer file more than once.
  • incomplete records
    • blank LoggingDates (this was a problem bringing in data from a northwest-based regulator)
    • blank levels
You'd think including StaticWaterDepth would be a good idea, but this introduces two problems:
  1. What to do when the DryWellFlag is true -- include a sounding for StaticWaterDepth?  Fake a value?
  2. We're guarding against duplicate WellID (LocationName/ScreenIntervalID) entries for the same date -- another issue I've seen importing data from a variety of sources is conflicting info for the same date/well -- so you want to catch imports like this:



(Wrinkle: while I could have -- probably should have -- made this the PrimaryKey (ensuring no nulls or duplicates in the future, and probably better compatibility with Access 2010), calling it a UniqueKey was easier because I could ignore nulls for a moment; save the index; go fix duplicates and Null ScreenIntervalIDs; and come back to enforce the index.)


Now to implement this in our template DB.

Tuesday, December 8, 2009

Spreadsheets are not databases.

Normalization -- what is it, and why is it important?

What:
A series of rules -- that's what database normalization boils down to.  I have had a terrible time trying to distill the "why" of database normalization, largely because I get all excited about the "what!"  So, in more concise terms than I can muster, enter the Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization

Why:
Databases start small, often with an Excel spreadsheet.  But -- this is important --  they're designed to get big.  Because many of us manage datasets with more than 60,000 records -- or will at some point.

How do these rules help big data sets?  Two ways:
  • managing data "types"
  • enforcing rules within a row, or record
  • enforcing rules between rows and
A data type is a date, a currency amount, etc; think of Excel's "format" function and you get the idea.  This rule persists throughout the column

Three levels of normalization:
http://www.troubleshooters.com/littstip/ltnorm.html

More depth on our target level of normalization:
http://en.wikipedia.org/wiki/Third_normal_form

Important translations:
Column => Field
Row => Record
So that's where we'll begin.  I'm going to go hop on a plane in a minute.

Thursday, November 26, 2009

SharePoint and the MS Bug Database

Set up the default template MS Bug tracking database on GSI's SharePoint server today. We support half a dozen Access databases here, and hope to use this to filter the frequently-unstructured feedback and feature requests.

Video training for the Bug database:
http://office.microsoft.com/en-us/help/HA103507191033.aspx

Nicely-created interface by MS.

Wednesday, November 25, 2009

Creating Charts in Access 2007

A coworker Ted from GSI used the Access 2000 Chart OCE tool for some nice-looking bar charts in a legacy database; when I looked for the tool in 2007 it was less-than-obvious. However, after playing around with the toolsets for half a day I discovered that it's basically the same graphing engine available in Excel -- just with a terrible interface.


This article summarizes some of the shortcomings (one needs to tweak their own query regardless of how the original is built in the interface), and does a better job than I would've showing you where to start:
http://www.vb123.com.au/toolbox/09_access/access2007charting.htm

To see this in action at GSI check out the GWMA db > hydrograph > frmHydrographByDate > select your well and date range, load it, and click the "Chart" button at the bottom of the page (launches frmHydrographChart).
 


Shortcoming -- since I rely on a scalar query to pick the most recent measured elevation for each well, Jet cannot create a crosstab of this data so it's difficult to compare multiple wells in this interface without exporting to Excel.

Thursday, November 19, 2009

Automated PDF creation

Recently had a set of Excel charts (hydrographs) to print to one PDF, found it more challenging than I thought.

I had exported a water elevation query (~8,500 records) to Excel that looked like this:



...then macro'd new charts based on changes in row data, and - like magic - created 60+ line scatterplots.  Whee!  Thinking back, I'd do this differently next time.  But "it's consulting" so it's time to keep the head down and finish the job...

This is where I learned that selecting simply trying to print the entire workbook to PDF creates, unfortunately, as separate PDFs as you had charts!  (Not to mention the data.)  Sigh.

Enter google and this article: Using VBA for Excel and PDFCreator to print multiple pages to ONE pdf.
http://www.excelguru.ca/node/21

Wednesday, September 2, 2009

After throwing this post (http://groups.google.com/group/comp.databases.ms-access/browse_thread/thread/4d0d579444392778#) on msaccess usenet groups, I found a new strategy for grouping and sorting data in access:


Don't.


Wednesday, July 15, 2009

MS Discussion Groups.

Search engine sorta sucks, but there are some sharp folk advising in this newsgroup:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access

We generally all struggle with the same twelve things in Access ... so it's nice to keep such a reference around.

Now I need to sort out how to open a pre-formatted PivotChart/Table with new query contents. Shouldn't be too tough, eh? ;-)

Sunday, July 12, 2009

Purpose.

Began this blog as a personal reference: a place to post links, troubleshooting algorithms, pearls of insight that can be a challenge to track.

Seems there are pretty evident patterns in environmental data management (EDM), and the several firms I've worked with over the last seven years generally handle things the same way: as long as it's billable there's no reason to leverage repeated 'business' logic. I'm trying to modularize these repeated tasks.

Hope this may become a collaborative, probably local, tool when it contains enough value.
Home | My Schedule (Free/Busy) | Professional CV | Learn how to Work Smarter

A Little More Background

Friends & Followers