Access Database Basics

What is a database?

In its simplest form, a database is a tool for collecting and organizing information. Databases can store information and are used all over the world for a variety of tasks and duties. There are many different types of Databases and they each have their own positives and negatives - however one of the most common in use today is Microsoft's Access Database.

As this product integrates closely with the other products in the Microsoft Office range it has some inherent ease of startup in that some of the menu commands and ribbons are in comfortable and familiar places.

Databases started as a way of better organizing the data that people previously tracked by use of spreadsheets and lists. With an abiilty to have a much greater analysis of the data available it simplified the massive spreadsheets that people were accustomed to building for there day to day work.

You can think of a database as a simple container of objects. As such a database can hold data in a variety of different formats and is not limited to simple text. An Access database is known as a "flat file" database - this basically means that it stores all of the information in a specific database in one single file. So all the tables, forms, reports etc... are all actually combined in on single .accdb file (note in earlier versions of Access this file extension would have been .mdb).

Tables in an Access database

At first glance you would be very confused with tables in Access as they are the same as Excel worksheets.  However this is their strength as the familiarity you already have with Excel will enable you to utilize tables very easily and to also import the data or spreadsheet directly into the database table.

The main difference between your spreadsheet and the database table is how the information is organized.  Data in Access is organized in a unique manner and information about a specific item should only be entered once to ensure that you do not have redundancy.  For example data about products would be entered into one table and the corresponding salesperson information would be entered into another table.  This process is called normalization.

Table rows are referred to as records, with each record being an individual piece of information.  However each record can have many associated fields (the table columns) so that you have the "full picture" for each item that you are trying to record.  So assuming that you have setup your salesperson table, each record (row) would contain information about a different salesperson with each field (column) having some more detail about them - such as first name, last name, phone number, email address, mailing address etc... 

Each field can have a different data type allowing you even more personalization and customization so that you can specify text, date, number or any other type.

Forms in an Access Database

Forms simplify the entry of data.  Once you've built your initial database and structure, you don't want to have to revisit those same tables again and you definitely do not want your users accessing the tables directly.  The way to ensure that they are able to edit and add information to your database is through the use of Forms.

Forms often contain command buttons and other controls that perform various tasks and you can use these controls to open other forms or reports and by utilizing your forms in a logical fashion you "tie together" many different tables enhancing the capabilities of your database.

Reports in an Access Database

Reports are basically the output of your database.  While its fun to enter data, you actually want to utilize that information in a certain manner and by using reports you are able to format, summarize and present data in a clear and logical fashion.

Generally each report is built to answer a specific question - for example how many sales did person "y" make?  But you can also make it a lot more generic - how many sales did team "x" make?

A database report is a "real time" representation of the information that is in the database.  Depending on the size of your database you can run a report at any time - I would suggest you wait till off hours to run significant reports as if your database is very large, this could impact the speed and responsiveness of your application and impact your users.

Queries in an Access Database

Queries are used to "pull" your data.  Just like a form is an interface used to put new data into your tables, queries are used to pull data from your tables so that it is presented to you in a meaningful fashion.  You can think of the Query as being similar to the formula's that you would have used in Excel but because you're able to pull data spread across several different tables, the functionality is significantly greater.

Queries let you add criteria to "filter" the data down to just the records you want and in addition to this, Queries also give you the ability to update data in the underlying table also!  They are an extremely powerful tool and come in two different flavors:

  • A select query - pull and forget.  A select query retrieves the information that you are interested in and makes it available for your use.  The results from a select query can be viewed on your screen, printed out or copied.  In addition you can use the results from a select query as the input into another query which really builds on the capabilities of your database.
  • A action query - pretty self explanatory really.  An action query does something with the output of the data.  This could be adding or deleting data from an existing table or even creating a new table!

Macros in Access

Macros are a way of enhancing the functionality in your database.  For example a simple macro would be to run a report when you press a button in your form. 

However macro's can be much more complex and with the abiilty to "record" macro's in both Excel and Access you can simplify your overall data entry experience greatly!   Most database operations that you do manually can be automated by using macros, so they can be great time-saving devices.


nicomp profile image

nicomp 5 years ago from Ohio, USA

Nice overview and intro to Access. The form tool and the report tool are still market leaders even after all these years.

My SciFi Life profile image

My SciFi Life 5 years ago from London, UK Author

Hi Nicomp - thanks for reading. Yes I've taken some courses on Access and other tools and the ease of use and functionality in Access definitely makes it stand out for me.

My SciFi Life profile image

My SciFi Life 5 years ago from London, UK Author

Hi Nicomp - thanks for reading. Yes I've taken some courses on Access and other tools and the ease of use and functionality in Access definitely makes it stand out for me.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.

    Click to Rate This Article