Microsoft Access Tutorial




MS Access is frequently used by small companies to create simple database solutions. Using Access, you don’t have to know too much about databases and will still be able to easily construct simple, aesthetically pleasing database solutions. This file has hyperlinks to some reference material. The bulk of the material that follows was copied and modified from the following websites: and


Part 0: Databases


A database is a collection of related information. The database is the container for the data and associated objects.  Objects include tables, queries, forms, reports, macros, and modules. 


To create a new database in Access 2010, start MS Access 2010, and select New->Blank Database. Fill out the filename for your database (yourlastname_labX.accdb for this class) and, if needed,  change the directory where the database is saved.



Part 1: Tables


In Access, the “table” holds the actual data, so we will begin your review with basic table operations. Below are a few useful definitions.

  • A table is a grouping of related data organized in fields (columns) and records (rows) on a datasheet. By using a common field in two tables, the data can be combined. Many tables can be stored in a single database.
  • A field is a column on a datasheet and defines a data type for a set of values in a table. For a mailing list table might include fields for first name, last name, address, city, state, zip code, and telephone number.
  • A record in a row on a datasheet and is a set of values defined by fields. In a mailing list table, each record would contain the data for one person as specified by the intersecting fields.
  • The primary key is one fields or a combination of fields that holds only unique values and can identify a particular record. For example, in a Midshipman table, the "alpha" can be declared as the primary key.
  • Design View provides the tools for creating fields in a table.
  • Datasheet View allows you to update, edit, and delete information(records) from a table.


Introduction to Tables

Tables are grids that store information in a database similar to the way an Excel worksheet stores information in a workbook. Access provides several ways to create a table. The default one is by using Datasheet View (see below), but we will use the Design View.

Create table in Design view will allow you to create the fields of the table. This is the most common way of creating a table and is explained in detail below.

Create a Table in Design View

Design view allows you to build a table from scratch and set or change every available property for each field. You can also open existing tables in Design view and add, remove, or change fields.

Callout 1 On the View group in the navigation bar, click Design View. You will be prompted to give a name to the table (give a meaningful name) and save it.

Callout 2 In the Field Name column of the designer, enter the names of your table fields. By default, each new table has a first field called ID that is the primary key for the table. You can delete that field if you want to create your own primary key (right-click and select Delete Rows from the menu) .

Callout 3 In the Data Type column, use the list next to a field name to choose a data type for that field.

Callout 4 Optionally, use the Field Properties pane to set properties for individual fields.

See below for more details:

Design View will allow you to define the fields in the table before adding any data to the datasheet. The window is divided into two parts: a top pane for entering the field name, data type, and an option description of the field, and a bottom pane for specifying field properties.

  • Field Name - This is the name of the field and should represent the contents of the field such as "Name", "Address", "Final Grade", etc. The name can not exceed 64 characters in length and may include spaces.
  • Data Type is the type of value that will be entered into the fields.
    • Text - The default type, text type allows any combination of letters and numbers up to a maximum of 255 characters per field record.
    • Memo - A text type that stores up to 64,000 characters.
    • Number - Any number can be stored.
    • Date/Time - A date, time, or combination of both.
    • Currency - Monetary values that can be set up to automatically include a dollar sign ($) and correct decimal and comma positions.
    • AutoNumber - When a new record is created, Access will automatically assign a unique integer to the record in this field. From the General options, select Increment if the numbers should be assigned in order or random if any random number should be chosen. Since every record in a datasheet must include at least one field that distinguishes it from all others, this is a useful data type to use if the existing data will not produce such values.
    • Yes/No - Use this option for True/False, Yes/No, On/Off, or other values that must be only one of two.
    • OLE Object - An OLE (Object Linking and Embedding) object is a sound, picture, or other object such as a Word document or Excel spreadsheet that is created in another program. Use this data type to embed an OLE object or link to the object in the database.
    • Hyperlink - A hyperlink will link to an Internet or Intranet site, or another location in the database. The data consists of up to four parts each separated by the pound sign (#): DisplayText#Address#SubAddress#ScreenTip. The Address is the only required part of the string.
  • Description (optional) - Enter a brief description of what the contents of the field are.
  • Field Properties - Select any pertinent properties for the field from the bottom pane.

Field Properties

Properties for each field are set from the bottom pane of the Design View window.

  • Field Size is used to set the number of characters needed in a text or number field. The default field size for the text type is 255 characters. If the records in the field will only have two or three characters, you can change the size of the field to save disk space or prevent entry errors by limiting the number of characters allowed. Likewise, if the field will require more than 255 characters, enter a number up to 255. The field size is set in exact characters for Text type, but options are give for numbers:
    • Byte - Positive integers between 1 and 255
    • Integer - Positive and negative integers between -32,768 and 32,768
    • Long Integer (default) - Larger positive and negative integers between -2 billion and 2 billion.
    • Single - Single-precision floating-point number
    • Double - Double-precision floating-point number
    • Decimal - Allows for Precision and Scale property control
  • Format conforms the data in the field to the same format when it is entered into the datasheet. For text and memo fields, this property has two parts that are separated by a semicolon. The first part of the property is used to apply to the field and the second applies to empty fields.

    Text and memo format.

Text Format


Datasheet Entry






@ indicates a required
character or space




& indicates an optional
character or space




< converts characters to lowercase




> converts characters to uppercase




\ adds characters to the end

@;"No Data Entered"




@;"No Data Entered"


No Data Entered


  • Number format. Select one of the preset options from the drop down menu or construct a custom format using symbols explained below:

Number Format


Datasheet Entry






0 is a placeholder that displays a digit or 0 if there is none.
# is a placeholder that displays a digit or nothing if there is none.







% multiplies the number by 100 and added a percent sign

  • Currency format. This formatting consists of four parts separated by semicolons:
    format for positive numbers; format for negative numbers; format for zero values; format for Null values.

Currency Format




Positive values will be normal currency format, negative numbers will be red in parentheses, zero is entered for zero values, and "none" will be written for Null values.

  • Date format. In the table below, the value "1/1/01" is entered into the datasheet, and the following values are displayed as a result of the different assigned formats.

Date Format




dddd","mmmm d","yyyy

Monday, January 1, 2001

dddd, mmmm, and yyyy print the full day name, month name, and year

ddd","mmm ". " d", '"yy

Mon, Jan. 1, '01

ddd, mmm, and yy print the first three day letters, first three month letters, and last two year digits

"Today is " dddd

Today is Monday


h:n:s: AM/PM

12:00:00 AM

"n" is used for minutes to
avoid confusion with months

  • Yes/No fields are displayed as check boxes by default on the datasheet. To change the formatting of these fields, first click the Lookup tab and change the Display Control to a text box. Go back to the General tab choices to make formatting changes. The formatting is designated in three sections separated by semicolons. The first section does not contain anything but the semicolon must be included. The second section specifies formatting for Yes values and the third for No values.

Yes/No Format




Prints "Yes" in green or "No" in red

  • Default Value - There may be cases where the value of a field will usually be the same for all records. In this case, a changeable default value can be set to prevent typing the same thing numerous times. Set the Default Value property.


Creating indexes allows Access to query and sort records faster. To set an indexed field, select a field that is commonly searched and change the Indexed property to Yes (Duplicates OK) if multiple entries of the same data value are allowed or Yes (No Duplicates) to prevent duplicates.

Validation Rule

Validation Rules specify requirements for the data entered in the worksheet. Examples of field validation rules include:

 <> 0  to not allow zero values,

 >= 0 to only allow numbers that are greater or equal to 0

="A" or = "B" to only allow value A or value B

???  to allow only all data strings three characters in length, etc.

You can click the expression builder ("...") button at the end of the Validation Rule box to write the validation rule.

Validation Text

A customized message can be displayed to the user when data that violates the rule setting is entered, so the user knows how to correct the errors.

Input Masks

An input mask controls the value of a record and sets it in a specific format. They are similar to the Format property, but instead display the format on the datasheet before the data is entered. For example, a telephone number field can formatted with an input mask to accept ten digits that are automatically formatted as "(555) 123-4567". The blank field would look like (___) ___-____. An an input mask to a field by following these steps:

  • In design view, place the cursor in the field that the input mask will be applied to.
  • Click in the white space following Input Mask under the General tab.
  • Click the "..." button to use the wizard or enter the mask, (@@@) @@@-@@@@, into the field provided. The following symbols can be used to create an input mask from scratch:

Input Mask Symbols




Letter or digit


A digit 0 through 9 without a + or - sign and with blanks displayed as zeros


Same as 0 with blanks displayed as spaces


Same as 9 with +/- signs




Letter A through Z

C or &

Character or space


Convert letters to lower case


Convert letters to upper case

Primary Key

Every record in a table must have a primary key that differentiates it from every other record in the table. In some cases, it is only necessary to designate an existing field as the primary key if you are certain that every record in the table will have a different value for that particular field. A social security number is an example of a record whose values will only appear once in a database table.

Designate the primary key field by right-clicking on the record (or records) and selecting Primary Key from the shortcut menu or select Edit|Primary Key from the menu bar. The primary key field will be noted with a key image to the left. To remove a primary key, repeat one of these steps.

If none of the existing fields in the table will produce unique values for every record, a separate field must be added. Access will prompt you to create this type of field at the beginning of the table the first time you save the table and a primary key field has not been assigned. The field is named "ID" and the data type is "autonumber". Since this extra field serves no purpose to you as the user, the autonumber type automatically updates whenever a record is added so there is no extra work on your part.

Adding Records

Add new records to the table in datasheet view by typing in the record beside the asterisk (*) that marks the new record. You can also click the new record button at the bottom of the datasheet to skip to the last empty record.

[Datasheet View]

Editing Records

To edit records, simply place the cursor in the record that is to be edited and make the necessary changes. Use the arrow keys to move through the record grid. The previous, next, first, and last record buttons at the bottom of the datasheet are helpful in maneuvering through the datasheet.

Deleting Records

Delete a record on a datasheet by placing the cursor in any field of the record row and select Edit|Delete Record from the menu bar or click the Delete Record button on the datasheet toolbar.

Adding and Deleting Columns

Although it is best to add new fields (displayed as columns in the datasheet) in design view because more options are available, they can also be quickly added in datasheet view. Highlight the column that the new column should appear to the left of by clicking its label at the top of the datasheet and select Insert|Column from the menu bar. Entire columns can be deleted by placing the cursor in the column and selecting Edit|Delete Column from the menu bar.


Part 2:  Forms

Forms allow you to control the look and feel of the screen for the input of data and the reports generated. 

Form Views
There are three ways to view forms in Access:

Design View

Allows you to design a form that includes a header, a footer, and details in the form.  You can also add images and control which fields appear on the form.

Form View

This is a dynamic page which allows the user to enter and edit data or navigate through data in a field. 

Layout View

This view allows you to design the form and manipulate data.


Create a Form
You can create a form from a table or a query.  To create a form:

  • Click the Create tab
  • Option 1: Click the Form button - this will create a form with all the fields in the table already in the form.
  • Option 2: Click the Form Design button to create a blank form

Form Design Button

  • Click the Format tab
  • Click the Add Existing Fields Button
  • In the Field List box on the right, click and drag the fields you would like on the form

Add Existing Fields Button and Dialog Box

  • To add command buttons to your form, while in Design View, select the “Button” icon from the "Controls" group, and then click on your form. From the Command Wizard window that shows up, select the action you want associated with your button: Go To Next Record (Record Navigation category), Add New Record (from Record Operations category), Delete Record (Record Operations category), Quit Application (Application category), Run Query (Miscellaneous category), etc.


To preview the form:


  • Click the Views button on the Home tab
  • Click the Form View button

Form View Button


Adding Records Using A Form

Input data into the table by filling out the fields of the form. Press the Tab key to move from field to field and create a new record by clicking Tab after the last field of the last record. A new record can also be created at any time by clicking the New Record button new record buttonat the bottom of the form window, or the appropriate Add Record button, if one was created for the form. Records are automatically saved as they are entered so no additional manual saving needs to be executed.

[Form View]

Editing Forms

The follow points may be helpful when modifying forms in Design View.

  • Grid lines - By default, a series of lines and dots underlay the form in Design View so form elements can be easily aligned. To toggle this feature on and off select View|Grid from the menu bar.
  • Snap to Grid - Select Format|Snap to Grid to align form objects with the grid to allow easy alignment of form objects or uncheck this feature to allow objects to float freely between the grid lines and dots.
  • Resizing Objects - Form objects can be resized by clicking and dragging the handles on the edges and corners of the element with the mouse.
  • Change form object type - To easily change the type of form object without having to create a new one, right click on the object with the mouse and select Change To and select an available object type from the list.
  • Label/object alignment - Each form object and its corresponding label are bounded and will move together when either one is moved with the mouse. However, to change the position of the object and label in relation to each other (to move the label closer to a text box, for example), click and drag the large handle at the top, left corner of the object or label.
  • Tab order - Alter the tab order of the objects on the form by selecting View|Tab Order... from the menu bar. Click the gray box before the row you would like to change in the tab order, drag it to a new location, and release the mouse button.
    [Tab Order dialog box]
  • Form Appearance - Change the background color of the form by clicking the Fill/Back Color button on the formatting toolbar and click one of the color swatches on the palette. Change the color of individual form objects by highlighting one and selecting a color from the Font/Fore Color palette on the formatting toolbar. The font and size, font effect, font alignment, border around each object, the border width, and a special effect can also be modified using the formatting toolbar:
    [formatting toolbar]
  • Page Header and Footer - Headers and footers added to a form will only appear when it is printed. Access these sections by selecting View|Page Header/Footer on the menu bar. Page numbers can also be added to these sections by selecting Insert|Page Numbers. A date and time can be added from Insert|Date and Time.... Select View|Page Header/Footer again to hide these sections from view in Design View.

Part 3: Queries

Queries select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet.


To design a query using the Query Design Button:

  • Click the Query Design Button on the Create tab
  • Select the tables that you would like to query
  • Click Add

Add Query Tables

  • Double click the name of the field you would like to query
  • Repeat this process for as many fields as you would like in the query

Query Formulate Page


Query Criteria
Query criteria are search conditions used in a query to retrieve specific data.  You can set query criteria to be a specific number or data set, or you can set the criteria to be a range of data.

To specify search criteria:

  • Click the query that you wish to add conditions
  • Type in the appropriate query criteria in the Criteria Box for the field that has constraints. For example, if the ID needs to be "Smith", you would have the example below:

Query Criteria

  • Enter the criteria for the query in the Criteria: field. The following table provides examples for some of the wildcard symbols and arithmetic operators that may be used. The Expression Builder [Expression Builder button]can also be used to assist in writing the expressions.

Query Wildcards and Expression Operators

Wildcard / Operator


? Street

The question mark is a wildcard that takes the place of a single letter.

43th *

The asterisk is the wildcard that represents a number of characters.


Value less than 100


Value greater than or equal to 1


Not equal to (all states besides Florida)

Between 1 and 10

Numbers between 1 and 10

Is Null
Is Not Null

Finds records with no value
or all records that have a value

Like "a*"

All words beginning with "a"

>0 And <=10

All numbers greater than 0 and less than 10

"Bob" Or "Jane"

Values are Bob or Jane

Note: Logical OR vs. Logical AND.   If you put search criteria for different fields all on the same horizontal line, then the criteria is interpreted as joined by logical “AND”.   If you place criteria on a line below the line of another criteria, then the two criteria are interpreted as joined by a logical ‘OR’.

  • Click Run if you want to execute the query and see the results:

Run Query Button


  • Save the query by clicking the Save button.


Part 4: Reports

Reports are a means to view and analyze large amounts of data from underlying tables or queries.    You can use the Report Wizard or create a custom report that meets your specific needs.

Report Views
Reports can be displayed in four views:

Design View

This view provides you with the structure of your report.  You can add, modify or delete components of the report but you cannot manipulate the data in the tables associated with the report.

Report View

This view allows you to view the data from the table but not to change any layout of the report.

Layout View

This view allows you to see data from the table and add, modify, and delete components of the report.

Print Preview

This view allows you to see what your report will look like when it is printed.

To change report views:

  • Click the View button on the Home tab

Report View Button

Create a Report;

To create a basic report based on the data in the current query or table:

  • While the Table or the Query on which you want to base your report is opened and the tab is active in Access, click the Report button on the Create tab
  • Change to Design View to customize your report if needed
  • If you want to change the source of the date displayed in the report, change to Design View, select “Property Sheet” from the toolbar and modify the “Record Source” for the “Report” object to be the table or query desired.


To create a blank report:

  • Click the Blank Report button on the Create tab

Report Button

  • Click the Add Existing Fields button
  • From the field list, Click and drag the fields to the report

Add Fields to Report