If you've looked for a job online, or searched for a book at your local library, you know
first-hand what databases can do. But do you know how they work? What goes on behind the
scenes?
In Microsoft Access, databases are made up of four simple objects: tables, forms, queries,
and reports. Together, these objects allow you to do all
the things you associate with using a database - from storing and entering more and more
information, to analyzing and compiling search results.
By now, you should know that a database is essentially a collection of data organized
into many connected lists (or tables).
That puts tables at the heart of any database - the place where your information is stored.
This table is from an actual Access database - mine.
It's a list of customers - one of the many things I keep on file to help me run my business,
a specialty bakery.
The familiar layout of horizontal rows and vertical columns takes on a new meaning here.
In Access, a row is more than a row - it's a record. Just like these are more than names
and addresses - they're people, some of them my favorite customers.
Each record begins with a unique ID number and spans several fields, including (in this
example) first name, last name, street address, and more.
Notice I used the word "field" instead of "column." In Access, fields are designed to
organize the contents of your records without actually breaking them up.
That means every entry in the First Name field is going to be a name and every entry in
the Street Address field is going to be an address
but no one will ever become separated from their record - that's their personal information.
While tables are responsible for storing all this data, the other three objects - forms,
queries, and reports - let you work with it.
Forms are used for entering, modifying, and viewing records.
Ever had to fill out a form while visiting the doctor's office or applying for a job?
Forms are used so often because they're an easy way to guide people into entering data
correctly.
When you or someone else uses one of your forms in Access, that data goes exactly where
you want it to go. This form makes it easy to add a record for a new customer
or edit an existing one. Anyone can do it - without ever having to face the size and
scope of that table, or the possibility of making a mistake.
Queries give you a way to search for and compile data from one or more of your tables.
Running a query is like asking a detailed question of your database - or doing an advanced
search on the internet.
When you build a query in Access, you're defining specific search conditions to find exactly
the data you want. A query could help you find the name and number of every customer
who's made a purchase in the last week, for example.
That's something you might not be able to find just by looking at the records in your
table.
Reports give you the ability to present your data - from any table or query - in print.
If you've ever received a printout of an invoice, or an official school transcript, you've seen
a database report.
They're useful because they let you present parts of your database in a way that's easy
to understand even easy on the eyes, with colors, graphics, and other things that you
can customize.
So. Now that you know how each object can be used, any idea how they work together?
It helps to remember that they all work with the same data - the data stored in your tables.
Let's take a look at a real-life example.
If you've ever used an electronic card catalog to search for a book at the library, it probably
looked something like this
Right now, I'm entering my search terms into what is actually a form.
When I press this button, the database is going to create and run a query based on what
I'm looking for - books in the series The Lord of the Rings.
When the database finishes searching all of its tables, it returns a report of the records
that matched - in this case, a list of books. All I have to do is click a book to learn
its location and status.
See how the objects all work together? First, I filled out the form then queried the database
to search for the book. The query was able to pull from all of those tables to produce
a report.
Imagine what it would be like if we didn't have these tools.
What should be a simple task would become incredibly complicated and time-consuming.
Understanding the four Access objects and how they work together can make even the most
complicated tasks fairly user-friendly.