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.