LIS 506 Assignment 3 - MS Access Database

For this assignment you will create an MS Access lis506.mdb database file. Upload the file to some place on your ubunix webspace, and post the url to the file in the Assignment 3 Discussion Board

Part 1: Tables

Create a Microsoft Access database containing two tables:

Table books
This table will be used for the acquisition department of a special library. This table should include the following fields:
You need to determine and set up the primary key field(s).
Set up validity checks for the table as follows:
Load the data from the books.txt file provided. Then manually enter four records. Your table should have 216 records.
Table departments
This table will be used to match a book to one or more departments. This table should include the following fields:
The balance field's value should be a number larger than 0.

Here are the four records you need to manually entered into your table:

department_id departmentName annualBudget balance
EDU Graduate School of Education 2000 560
COMP Department of Computer Science 5000 1898.13
LIS Department of Library and Information Studies 1000 1298
HIST History Department 1500 90.1

Part 2: Form

Create a form for the books table data entry.

Part 3: Query

Create Query1 that returns all titles that were ordered with price (per copy) more than $40 and were charged to the "LIS" or "EDU" departments. The query result table should show the following fields in the sequence indicated:

  1. title
  2. unitprice
  3. order_date
  4. department_name

Part 4: Report

Prepare a report report1 of books which contains the following fields:

Records in the report should be grouped by department_id, and sorted by author (ascending), subsorted by title (ascending), and subsubsorted by publication_year (descending). The average price of all books ordered by each department should be presented in the appropriate position. Complete book titles should be displayed. The report should have a proper title and your name at the top of the report and a page number on each page. It should be well laid out, in portrait mode.

Home Assignment 1 Assignment 2 Assignment 3 Assignment 4 Assignment 5 Final Project