Getting Starting with Access 2007

Access is a relational database development tool. Databases allow you to collect information for the purposes of storing, searching and retrieving that information. Most often, data is stored in tables, and certain fields within those tables will relate to fields in other tables. Relational databases allow you to group data into one or more specific tables that are related to one another using fields common to each related table. After you define relationships between different data tables within your database, you can use these relationships to perform complex searches and produce detailed reports. This will become more evident as you work through the tutorial.

This tutorial will cover the basic components of Access: tables, forms, queries, and reports. As you work through the tutorial you should see how all four components work together.

About this Tutorial

What you will learn

 How to create tables in design view  How to create forms
 How to enter data in datasheet view      How to add sub forms
 How to move data  How to create queries 
 How to define relationships among tables     How to create reports

What you need to know before getting started
  • You should have knowledge of the Windows operating system. If you are not familiar with it, we recommend that you check out and complete the Basics of A Windows Environment available from the Computer Resource Library. This tutorial covers the basic skills required to manage a Windows machine and will prepare you to understand and complete this tutorial.
  •  You should know how to access your U drive on Sweeney. If you do not know what the U drive is or how to use it, ask a consultant for assistance.

Getting started

Before you can create your database, you must designate a location in which to save your database files. It is Log onto a Windows computer using your Eaglenet username and password.

1.      Double click the Applications folder on the desktop.

2.      Inside the folder, locate and double click on the Microsoft Access icon to start the program.

3.      Near the center of the screen a ‘Getting Started’ menu will be visible, Click on the Blank Database Icon, to create a blank Database. 


4.      To the right of the screen it will ask you where you want to save.

5.      Direct the file to your U drive or Zip Disk. (Figure 2).

6.      Type “Customers.mdb” in the File name box (Figure 2).

7.      Click the Create button. The Database window should appear (Figure 2).

The Access 2007 Environment

You should now become familiar with the Access environment. Review the Ribbon shown in Figure 4 to familiarize yourself with the options available. You will notice the toolbar, which is similar to any menu bar for any Microsoft program. The toolbar contains options that allow you to manipulate your database. The Access Toolbar contains the tools necessary to create tables, forms, reports, queries, and create relationships. Access 2007 allows you to have everything at your fingertips, making it easier to import data from other software such as Microsoft Excel.

In this project, you will create a database customers you spoke with during your next six shifts. In your database, you will track all calls, and record what was needed by the customer. You will note whether the customer is a student, faculty, administration or staff. You will be required to note basic information such as the location of their office, and a telephone number to reach them at.  This is just one example of a database that you can create in Access.

Before we Begin

Before a database can be built, it is necessary to determine what the database will include. For the purpose of this tutorial below is a check list of requirements that will need to be included in your database.

  • Tables
  • Forms
  • Queries
  • Reports

Next we will need to go over some of the views that you will need to know before building a database.

  • Design view: allows users to customize a table to meet their specifications.
  • Datasheet view: Allows you to see the physical data that has been entered. In a table this looks like an excel spreadsheet.
  • SQL view: SQL is the code behind the database. You will not be working with this view but it may be beneficial to look at some of the code you write with Access to get a feel for what you are doing.

Creating a table in Design View

 We will learn how to create a table in Design View so that you have the skills to either create your own custom table from scratch or modify an existing table. 

  1. In the Database window, a table is already open for you. In the top left hand corner near the Microsoft Button, is the view button. Select the design view 
  2. The field name identifies the information within the field; consider it a label of sorts. In the first row of the Field Name column, type Customer_ID and then press the Tab key to move to the Data Type field. (The Customer_ID is a unique number given to each product for purposes of identification).
  3. Defining the primary key: A primary key is one field that uniquely identifies each record in the table.  For this example, UPC is chosen as the primary key, or unique movie identifier, because every movie has a distinct UPC code. Several movies may have the same title, so using “Title” as the primary key could lead to problems. Many movies are produced in a year, so using “Year” would also cause difficulties, and so on.
    • Click the cursor once in the Customer_ID cell to select it.
    • Click the Primary Key button   in the toolbar.
    • You should notice that a small key button appears next to the Custober_ID cell.

  4. The Data Type defines what kind of information is included in the field. By default, AutoNumber is listed in the Data Type column. By Selecting AutoNumber, you are telling the computer to select a number for you. 
  5. Press Tab again to move to the Description column, this is where you will begin to build your database dictionary. Your database dictionary gives definitions of common database and SQL terms and phrases.
  6. In the Description column, type unique customer identification number

  1. In the Field Name column in the second row, type Customer_Name and press the Tab key to move to the next column.
  2.  Leave the Data Type as “Text.” Press Tab to move to the Description column.
  3. In the Description column, type name of customer and then press Tab.
  4. On the next row in the Field Name column  type Customer_Telephone 
  5.  In the Data Type column change it “Number”.
  6. Continue entering information it should look like the photo below. 

Saving your table

  1. Now that the table has been defined, you must save it. Close out of the table, it will prompt you to save.
  2. Type Customer in the Save Table box and click OK

Entering your data in Datasheet View

  1. From the menu bar choose View à Datasheet View. This shows your table in actual table format.

  2. The cursor is already flashing in the Customer_Name. Type Alexis Grenier and press Tab to advance to the Today’s_Date column and select today’s date. 
  3. Fill in the rest of the data, as shown in.You will notice that you will not be able to enter an Assigned_Tech_ID, this information will be filled in later.

Increasing Column Width 

  1. You may notice that your columns are not large enough to fully view the computer name.
  2. Place the mouse on the divider between the column labels. The mouse pointer changes into a bar with arrows on either side.
  3. Double-click the mouse and the column will automatically resize to fit the largest cell.
  4. Repeat this process for the all of the columns and then choose File -> Save.

Sorting Records

  1. In some cases you may wish to sort your records. For example, you may wish to sort your customers by name. To do so, move your mouse over the Year label until it turns into a bold, downward pointing arrow.
  2. Click the mouse once and the entire column is selected.
  3. Now click the Sort  A to Z button in the toolbar. Your records are now sorted alphabetical order. (NOTE: Unlike the sorting function in Excel, the data in each individual record will remain intact, without any extra effort from the user.)

Changing the Table Name 

  1. To change the table name, you must close the table by right clicking on the tab that says table 1.
  2. Right click where it says Table 1 under All Tables and chose to rename it to Customer.

Rearranging Field Order

Sometimes after you have designed your table, you decide that the fields aren’t in the order you want them to be. You can easily rearrange the order of fields.

  1. Place the pointer over the “Today’s_Date” column label until it becomes a bold down arrow again, and then click, hold and drag the column to where you want to position it.

Creating a New Table via Importing from Excel

  1. Close the customer
  2. Click External Data  on the Ribbon
  3. Select “Excel”
  4. Be sure that “import the source data into a new table in the current database” is selected 
  5. Click browse to locate the file.
  6. Choose the file and click open.

Relationships

In Access, a relationship determines how information stored in separate tables is brought together.  Relationships must be built before quires can be made.

Defining Relationships
  1. Click the Database Tools tab in the toolbar, then Relationships.
  2. Show table dialog box: Choose tables and click add , when they appear, drag Employee ID to Assigned tech.
  3. Save & close.

Linking your Employee Table to your Customer Table

  1. Rename Sheet1 to “Employee”.
  2. Go to design view and change ID to Employee_ID.
  3. For the Description, type Employee Number
  4. In the second row, for the Description, type Employee Name
  5. Continue writing descriptions. It should look something like below.

Creating Forms

  1. On the tool bar click Create.
  2. Click more forms and select Form Wizard.
  3. Once in the Form Wizard, be sure that the Table/Queries shows “Table: Customer”. 
  4. From the available fields choose all by clicking on the double arrow. 
  5. Choose next.
  6. Choose the layout to be Columnar, click Next.
  7. Choose the style that you prefer, click Next.
  8.  Choose to Open the form to view or enter information and click Finish.

Editing your Form's Layout

A form is made up of labels and fields that can be manipulated by the user in Design View. A label is typically used to describe the data that will be displayed in a particular field. A field is used to display data previously entered into a table. Both labels and fields can be deleted, moved or resized in design view, thus, allowing the user to fully customize the look of the form. Take a look at some of the layout modifications that can be made.
  1. Change the view to design view. Please note that it is important when creating a form that there is as little white space as possible.
  2. Next we will remove the links between the fields, this will make editing easier. To do this click on the four arrows near the top on the left hand side of your form.    
  3. Then click remove in the tool bar under the Arrange tab.
  4. Now we will make the entry fields more visible by sinking them in.
    •  Right click and choose properties if the Property Sheet is not on the right hand side of the screen.
    • Then select the right column by holding the shift key and clicking on each field.
    • Click on the Format tab on the property sheet and go to special effect. It will say flat to the right.
    • Change flat to sunken. 
    • Save, click on the Home tab on the tool bar and choose Form View.
      1. You will see that the entry boxes have now been sunken into the page to make for easier viewing. 
  5. Next we will make sure that the Left hand column can be viewed.   
    • To do this go back to design view. 
    • First select the left hand column again, then click on one of the gray boxes. Hold the mouse and drag it to the right. This will shrink the left column. 
    • Now you will be able to expand the right column by selecting the entire column, clicking on the top of the column and moving the entire column to the right. Now you can expand the column by clicking on the gray box and sliding the column to the left. 
  6. Continue to move around columns until you are satisfied with them. Remember you want as little white space as possible.
  7. Next change the names in the left hand column only by removing the underscores.

Creating Queries

Queries are "questions" you ask the database to help you find information. In this section, you will create a query to find what employees are working with each customer. 
  1. We are now going to find out what employees are helping each customer by creating a query. 
  2. Double-click Create Query in Design View under the create tab. The Query1 and Show Table windows appear. 
  3. Double click the Customer table and the Employee table. 
  4. Double click "customer on the Customer table and "name" on the Employee table to link the two. 
  5. Click the run button to get the results.
  6. You should now see your customers and who helped them or who the customer was assigned to during your shift.
  7. Close out of the query and save it as Cust_Emp_Q.

Reports

Creating a report provides a convenient way for you to prepare your data for display or printing. Reports are fully customizable and can be created totally by the user in Design View or with a little help from the Access Report Wizard. You can create reports from queries or from whole tables of data. In the example below, we will be creating a Report from the query that was created above, using the Report Wizard.

  1. Click on the create tab.
  2. Double-Click Create report by using wizard. 
  3. In the Report Wizard select Cust_Emp_Q from the drop-down menue.
  4. Click the >> button to select all the feilds. 
  5. Click the Next button. 
  6. Select by employee
  7. Click Next in the window about grouping and Next gain in the sorting window. Click Next agn when you are asked to order your records. 
  8. Select a style of your choice. 
  9. Click Next. 
  10. In the last window, type Employee to Customer.Collection Query. Click Finish
  11. The report based on your query is now available. Click the design view button to make any changes that you desire. Otherwise choose File, Save. 
  12. Close out of Access you have finished the basics.