IT360 Lab1: The Squadron Flight Schedule using Access

 

DUE: January 15, 2008, BEFORE start of lab

 

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 switchboard.   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 and create a new blank database called yourlastname_lab1.mdb, 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. Must be a date between 1/1/2008 and 12/31/2008

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. Thus, the first two flights of 10 Jan 2008 are :

            01/10/2008 – 1

            01/10/2008 – 2

     If a user enters 01/10/2008 - 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 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/2008 - 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 date 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.  Be sure to add command and navigation buttons using the toolbox to perform basic record navigation.  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: SwitchBoard

A form that is used to navigate to other forms and reports in the database is referred to as a switchboard. In most cases, you design a switchboard to open instead of the Database window (similar with the main page of a web site).  On the switchboard you place controls for only those objects to which you want the user to have access.  The screenshot below is a simple switchboard (it allows two operations, but you only have to allow one of them). 

 

Add a simple switchboard called SwitchboardFlighs to your database that will allow the user access to the ViewEditFlights form you created in Part 2. Create the switchboard as a form, then set the switchboard to open automatically when the user selects the database. You accomplish this using tools->startup from the database main page and setting Display Form/Page field to SwitchboardFlights.

 

Part 5. 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.

 

5.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.

 

5.b) Add the option of viewing the data (the Report you created) to the switchboard in Part 4.

 

Turn in (due BEFORE start of lab on January 15 , 2008):

Electronic:

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

Hard-copies:

  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. A print screen of the switchboard created in Part 4.
  6. For extra credit: a print screen of the report created in Part 5, and the new switchboard.