inls 461
Information Tools

Professor: Serena Fenton
School of Information and Library Science at UNC-Chapel Hill

Database Forms and Queries Assignment

Total possible Points: 100

  • Use the book lending database: bookLending.mdb for this assignment. It will be similar to what you created for the previous database assignment.
  • You will be creating three forms, entering data and creating 15 queries.

Below is the Relationship Window for the database. There are four tables.
The borrower has the first and last name in the same field, which is not the best database design, but we are trying to keep it simple.

relationships

I have taken advantage of the ability of Access' Lookup Wizard to enter display informative data from one form in a related form.

Example from the Loan table :

lookup

In the Loan table, the Book Title and the Borrower's name can be selected from a dropdown list.

This does mean though that the data must already be entered into the related tables for anything to show up in these fields.

Assignment Requirements and point value

Remember to compact your database: (10pts)
Toolbar > Tools > Options > select the General tab> check Compact on Close

Forms Borrower Form Author-Book Form Loan-borrower Form Enter data (records)
Queries        

1- Borrower Form (5 pts):

  • Create a columnar autoform for Borrower
  • Save as Borrower
  • Change style format to Ricepaper

example:

borrowers

2)Author-Book Entry Form (5 pts):

  • Create a book-author form using the wizard.
  • Use two tables for this: Books and Authors
  • Form(s) should contain all of the fields
  • Save as form with subform
  • Organize by Author
  • Sort by Author last name
  • Format as Ricepaper
  • Label the form: Author-Book form
  • Label the subform: Author-Book subform

Modify Author-Book Forms (using Design View) (5 pts):

  • To allow you to read all fields completely (no field left partially obscured)
  • Fields should be fully viewable
  • If there is more than one book per author, you should see at least part of the second field (so that you will know to scroll).

example:
(note, the fields were adjusted (isbn is stacked over title) to allow more room for the description

book-information

3) Loan-borrower for (5 pts):

  • Create a Loan-Borrower form using the wizard.
  • Use two tables for this: Loans and Borrowers
  • Form(s) should contain all of the fields from Loans and from Borrowers table use only the borrower_name, phone fields
  • Save as form with subform
  • Organize by Borrower
  • Sort by Borrower last name
  • Use Tabular layout for the subform
  • Format as Ricepaper
  • Label the form: Loan-Borrower form
  • Label the subform: Loan-Borrower subform

Modify Loan-borrower Forms (using Design View) (5 pts):

  • If there is more than one loan per borrower, you should see at least part of the second field (so that you will know to scroll).
  • Change the background to a custom color or image (to make it visually distinct from the book-author form)
  • Shorten the Title Loan Number to Loan #

example:

loan-borrowers

4) Create Records: 5 pts

Create at least 15 records for each table . You can create them in the tables, but you just made these cool forms - why not use them?

Be sure to give the borrowers a diverse set of locations, including 3 or 4 different states. You will be running queries which use state as the sort criteria (you also want to have more than one user per state.) Using Chapel Hill, NC as one city/state combination. Having some borrowers living in VA is also good.

Queries: 60 pts (4 pts each)

You can create the queries using the wizard or in the design view. You may need to modify any queries that are created in sing the wizard. Save each query with query number (below) include the '0')
(lose 10 pts if you do not label correctly!!)

number
 Query
01

hint:

 Show the names of all the borrowers and their states. Sort by borrower name.
Simple select query - save as query 01
02

 

hint:

 Show the names of all the borrowers who are in a certain state (i.e.VA); Sort by borrower name.

Two possible solutions:
Simple select query; use criteria
or
Parameter query, you need to give input infortmation that will guide the user, such as: use two letter abbreviation or input state: NC VA SC GA
save as query02
03

hint:

 Show all the information for all the borrowers who live in a certain city (i.e. Chapel Hill); Sort by Borrower name
Simple select query or
Parameter query, you need to give input infortmation that will guide the user
save as query03
04

hint:

 Show a count of borrowers by state; which state has the greatest number of borrowers?
See the exercise example on counting by genre
save as query04

05

hint:

 Show the titles of all the books that begin with a given letter.
This uses a wildcard criteria; see wildcards in your book - page 127
You need use one of the string operators on page 126.
save as query05
06

hint:

 Show the total value of all the books in the collection.
A very simple one field query using a basic mathematical operator
save as query06
07

hint:

 Show the average cost of a book in the collection.
A very simple one field query using a basic mathematical operator. You could even copy query 06 and adjust it appropriately.
save as query07
08

hint:

Show the most expensive book and the price.

This requires a two step query. Use this Microsoft tutorial as a pattern in creating your query. In the cdstore.mdb exercise handout, on page 11, I walk you through creating a two part query

You will be using this two step query structure in several of the queries in this assignment. It is not difficult, but it feels awkward when you first start out.

save as query08a and query08
09

hint:

 Show the all the books that cost less than $10.00
This is a simple select, using a math operator to establish the criteria
save as query09
10

 

hint:

 Show the name and address of the borrower with the greatest number of book loans (past and present)

You want to use the count function (in the totals field).

Create a select query of borrowers and loans; uses the Totals function to count a unique identifier in the loans (isbn borrower_id)

I will accept the entire loan record set (sorted descending; plus the borrower name and address) as the answer - this is a simple solution .

If you want to try for the most elegant solution, here is a three part solution:

  1. You want to count the loans first; - save as query10a
  2. then find the max of the numbers in 10a; save as query10b
  3. Then associate the name with the max and the borrower (you use both previous queries, plus the related table to get this to work correctly); save as query10
11

hint:

 Show the names and phone numbers of all borrowers who have book on loan presently. (this is a list of people who have borrowed, but not returned books)

This is a query where you want to restrict the criteria to "Is Null" or "Is Not Null"

For an example, see database: FamHist - This is a very simple genealogical database.

* PeopleWithBothParentsKnown has a "is not null" criteria
* PeopleWithUnmarriedParents has a "is null" criteria

This query makes the assumption that there everyone in your database has borrowed a book. You want to apply the null and not null to the appropriate date fields.

12

 

hint:

 Show titles and authors of all books that have been borrowed more than once.

This takes two queries:

1- create one query using the loan table (ISBN only) that sorts the loans or ISBN;
Save this as query12a

2-create a second query using the Authors table, the book table and query 13a;
Group query 12a by count, ascending.
Save this as query 12.

13

 

hint:

 Show title and author of the most recent loan and the name and phone number of the borrower.

You will need to create two queries for this.
Use this Microsoft tutorial as a pattern in creating your query:

Relevant section begins "Display only the oldest or most recent record". Just follow the directions.

  1. Your first query will only use the loan_id for steps 1-8 of the tutorial; save as query14a (field should create default name of lastOfloan_id)
  2. Your second query will use all of the tables and query14a;
    You must create this query in Design View (wizard will not work!)
  3. In the Design View, you have to manually create the relationship between loan_id ans lastOfloan_id; (just drag it across)
    Save as query14
  4. Add Fields as needed to satisfy the query requirements (name, loan date, author title and lastOfloan_id)
  5. Use totals button on lastOfloan_id to group by max
14
 Show title and author of the first loan, and the name and phone number of the borrower.
15
 For each loan of each book, show the book title and author, the borrower name and the date;
sort first by title and then by date.

To submit your assignment:

Zip the Access.mdb file. Submit through Blackboard's Assignment area.

 

revised October 10, 2006