Microsoft Access Basics

This guide is intended to help people with little or no experience using Access get acquainted with its basic features.  In this tutorial, you will learn how to create a simple relational database, with multiple tables.

Note: This guide is based on the desktop version of Access 2016 for Windows.  Earlier or different editions may function somewhat differently.

Access is a database program.  It is used primarily for storing data and giving easy access to those data.  A basic Access database stores information about like items with common attributes in tables.  Each item in an Access table is represented by a single record with a number of data fields containing descriptive information about that item. 

For example, if you were to create a database of books ordered for a library, you might end up with a table like the example below

Note that:

1. Each field (contained in the columns) represents a bit of information you might want about each book (e.g. Title, ISBN, Date Ordered, Date Received, Vendor and Cost.)
2. Each book would be represented by a single record with those six fields (each represented by a row on the table)
3. You could add an infinite number of books to this table, and it could store the same information about each.

How to Get Started on an Access Database

1. Determine what information you want in your database.  Each type of thing you want to store information about should be in its own table. Each field in that table should be a different piece of information about that one thing.  If you need to store information about more than one type of thing, you’ll want to create multiple tables.  (It’s worth looking at Microsoft’s article on Database Design Basics –specifically the “What is Good Database Design?” section.)
2. Open Access
3. On the opening screen (see below), single-click on “Blank Desktop Database.”

4. This will bring up a dialog box (see below) where you can name your database and choose what folder it goes into (by clicking the folder icon to the right of the file name field).  Once done, click “Create.”

5. Access will create a blank table with two columns.  To get started defining your fields, go to “Design View” from the dropdown “View” menu at the top left of the Access ribbon.  You will be asked to save your table first.  (Save it with a name that’s descriptive.  If we were to continue using the example from earlier, a good title might be “Books Ordered.”)  

6. This will bring you to a table with three columns titled “Field Name”, “Data Type” and “Description.”   Each row you create underneath represents the fields you will have in your table. (Access automatically creates the first field, titled “ID”.  It can be removed or changed, although it’s often wise to leave as is, for reasons to be discussed later). The field name column is where you enter the name for your data field (“ID” in the automatically generated field).  This name is what you will see at the head of the column in your actual database.  The data type column is where you define what type of data is allowed in this particular field for each record.  “Description”, can be used to provide the user with additional information about each data field, but is optional.  (Note: The key icon next the "ID" field below, means Access identifies that field as the "Primary Key."  This means that every record needs to have a value for this field and it needs to be unique.  This allows Access to have a way of distinguishing each individual record.  You can make another field the Primary Key, or get rid of the Primary Key, altogether, but it's essential for working with related tables.)    Create a new field by typing a name in the "Field Name" column, in the row below the existing fields.

7. Select a data type for your field.  The ones you are most likely to use are:

• Short Text (Formerly just “Text”).  Use this if you will be storing text data (names, places, etc…) in this field that you do not expect to exceed 255 characters.  Also use this for any numbers that you want treated as text (such as ISBN #s or Student ID #s.)
• Number.  Use this if you will be storing numerical data in this field. 
• Date/Time.  Use this for storing dates or times. 
• Currency.  Use this for currency. 
• Yes/No.  Use this for fields where the data is the answer to a binary question (there are Format options for “True/False”, and “On/Off”, as well).
• Lookup Wizard.  Select if you wish to provide a list of predetermined options for the user to choose from for this field’s data.  This will be described in greater depth later.

Note that when any data type is selected, it opens up a table at the bottom of the Access window, in the “Field Properties” box (see example, below).  This table gives a variety of options for customizing how your field works with the data input (for example, you can choose a date/time format or a currency type in the properties for fields of those types.)  If you wish any of your fields to require a value, find the “Required” row on its field properties, and select “Yes” from the drop-down menu.  This is often helpful in preventing users from forgetting to enter essential data.

8. Repeat steps 6 and 7 until you have all the necessary fields.
9. Define a primary key (optional).  (If used, this needs to be something unique to each record like the autonumbered “ID” field or a unique value the user enters.)
10. Save your table and return to datasheet view.
11. You can now start entering data.

Entering Data in an Access table

1. To create a new record, start typing data into the blank row beneath the current records (it should have a star (*) next to it.
2. To edit an existing record, simply click on the field of the record you wish to edit.
3. Tab or click to move between data fields.