IT360 Lab1: The Squadron Flight Schedule using Access

 

DUE: January 18, 2011 before class

 

Overview

 

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 lab is designed for you to learn some of the basics of Access.  You will build a solution to a simple problem using just a single table, a single form, and a few queries. For extra credit, you can also build a report.   There are ample online tutorials and the Access “Help” system is very detailed. Use any resources at your disposal to accomplish and understand the tasks required of this lab.  You may consult other students on using Access but all work should be your own (i.e. do not copy other students tables, forms, etc). 

 

Part 0: Create the database

Open Microsoft Access 2007 and create a new blank database called yourlastname_lab1.accdb, where yourlastname is your last name.

 

Part 1: Tables

The simple “flight schedule” database you will build for this lab requires only a single table.  A table should contain data for only a single “theme”, so the theme of this table is a flight event.  Table basics will be discussed in class and there is a tutorial on the course webpage covering Access basics.  You are responsible to be able to do the following:

Create a table called Flights with the following fields. Be sure data input by the user passes any validation requirements as specified in the following table:

 

Name

Type/format

           Required

Comment

FlightDate

Short date

Yes

“short date” (mm/dd/yyyy) format

EventNumber

integer 

yes

Flight number within a given day

PCLastName

text

yes

Last Name of Plane Commander

PCFirstName

text     

yes

First Name of Plane Commander

Takeoff

Short time

yes

“short time” format

Landing

Short time

yes

“short time” format

EventType

Text

yes

The only valid inputs are:

§  “PT” for pilot training

§  “MI” for mission

§  “CT” for crew training

§  “MC” for maintenance checks

Comments

Text

no

Lists training or mission information


Notes:

1.      If the user enters an invalid EventType, a message must be displayed explaining the valid options.

2.      The key fields together must uniquely identify each row in the table. A possible key is FlightDate and EventNumber combined  (select both fields and right-click while holding the “shift” key down to select “Primary Key”). Thus, the first two flights of 10 Jan 2010 are :

            01/11/2011 – 1

            01/11/2011 – 2

     If a user enters 01/11/2011 - 1 a second time, the database will issue an error message stating you are trying to enter a duplicate key.                                                          

 

Once you create the table structure, switch to the Datasheet view (or double-click the table name) and add several rows of data.  Try to duplicate the data in a key: if you use the date and event number as the key, and you enter 01/10/2011 - 1 twice you should get an error message. Likewise, you should get an error message for entering an improper EventType with an explanation of correct inputs.

 

Part 2: Data-Entry Forms

 

Forms provide the most flexible way for viewing, adding, editing, and deleting your data.  The user of your database should not see the raw table data or understand how to enter data into a table.  The data-entry form should be easy and intuitive for the user.  The tutorial provides an overview of forms.  You can use the form design view or the wizard to create forms.

 

Part 2 requires you to build a simple data entry form called ViewEditFlights for flight events.  The easiest way to do this is to use the Create - Form options from the menu, then select View - Design View to have the "design menu" show up. Use the "button" option to add command and navigation buttons to your form. Be sure to add "go to next", "go to previous", "add new record", "delete record", "save record" buttons to your form. Add some data using the input form and test your command buttons.  Below is an example of a data entry form for a flight schedule program

 


 

Part 3: Queries

The primary purpose of any database is to store and extract information. Using database tools you can easily obtain information to meet virtually any criteria you specify. A query is a question you ask about the information stored in your tables.  There are six types of Access queries, but for this lab you will work with just one – the “select query”.

 

Create queries that ask the following.  If you do not have much data in your database go ahead and add some additional records.  Save each of the queries for review by your instructor using the name given Query1, Query2, etc. 

 

Note that on the flight schedule there could be a field for “total time” for the flight.  This information is not recorded in the table. In fact, computed data should never be stored in the database. (Why?)

 

Part 4. Extra credit: Reports

Reports provide a flexible way for viewing and printing summarized information. They enable you to display information with the desired level of detail while letting you view or print your information in almost any format.  Reports can range from the very simple to build to fairly complex.  We will focus on the simple.  The Reports are similar to forms except they display results of a query.

 

a) Create a “flight schedule” similar with the one shown below using the “Query1” in Part 3 as the basis of the report.  You do not have to follow the same format as below. A simple listing of all the results of the query is enough. You can use either the design view or the wizard view to create the report.

 

 

Turn in:

Electronic (due BEFORE class on January 18, 2011):

  1. Upload the database you created, named yourlastname_lab1.accdb to Lab 1 assignment on Blackboard.

Hard-copies (due BEFORE class on January 18 , 2011):

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A print screen of the table created in Part 1, design view.
  3. A print screen of the form created in Part 2.
  4. A print screen for each query created in Part 3, design view
  5. For extra credit: a print screen of the report created in Part 4