Access Homework

Due date: April 21st, 2009 (11:59PM). Submit your database file (.accdb) to me by email.

For this assignment you will be creating your own Access databases for a used book store. The book store would, naturally, like to keep an inventory of their stock. A simple Excel spreadsheet isn't good enough for them, as they would like to easily perform complex queries. For example, a customer might ask, "Do you have any other books by this author?" or "Do you have anything that costs less than $3?" A database is a perfect fit for this kind of thing.

The following outlines the requirements. You must use the database structure that I provide. You are free to makeup your own sample data (aka sample books and inventory) but there are no advantages to not using the sample data. If you use your own sample data, it should have at least as many books as the sample data does and should be similar to the sample data (e.g., more than 1 copy of a certain book in various conditions).

1. Create a new Access Database (be sure to save it where you can find it later)
   1. Create a table that will hold each of the different books we will have available for sale
      1. Fill in data for the book’s ISBN number, genre, title, author, and number of pages for each book
      2. Make the ISBN number the primary key for this table, and delete the AutoNumber field if one exists.
   2. Create a second table for the inventory (these will represent the individual books for sale)
      1. Fill in data for each book’s ISBN, condition, and price
      2. For this table we can have more than one of the same book so make sure we are using an AutoNumber as each record’s primary ID.
2. Create automatic forms for both of the tables and give them non-default styles. You can tweak these if you want, but it’s not necessary.
3. Setup Access to be aware of the relationship between the two tables. Link them in the obvious way.
4. Create a query that simply shows the book titles and book authors ONLY (e.g., don't show ISBN, genre, etc).
5. Create a query that returns all the books in the inventory with their book information (title, etc.) as well as their individual information (condition & price). This will be a many-to-one detailed query. This query might return results which look like:
   1. "9780439554930, Fantasy, Harry Potter and the Sorcerer's Stone, J. K. Rowling, 309, Fair, $7.00"
   2. "9780439554930, Fantasy, Harry Potter and the Sorcerer's Stone, J. K. Rowling, 309, Good, $14.00"
   3. "9780439554930, Fantasy, Harry Potter and the Sorcerer's Stone, J. K. Rowling, 309, Poor, $4.00"
   4. etc. (not all results shown)
6. Create a query that returns a count of all the books in inventory grouped by genre. This will be a many-to-one summary query. A sample result might say:
   1. "Sci-Fi, 1" (because there is 1 copy of 2001: A Space Odyssey, and no other sci-fi books are in stock)
   2. "Non-Fiction, 3" (3 copies of Guadal Canal, no other non-fiction books exist)
   3. etc.
7. Generate a report for each of these queries

Bonus: Create a query, call it "Bonus Query" or something similar so I can find it, that uses conditions in some way. For example, the query could find all books by ONLY a certain author or all books in poor condition.

The total point value of this assignment is 100 points.

Here is some sample data for the books database:

Here is the sample data for the inventory: