DB Unit 2 Assignment

CS 100 Database

Assignment for unit 2

Mr. Patina can recognize many customers by sight, but he can't always remember their collecting habits. When a customer walks in the door he would like the ability to quickly review his or her purchasing habits so he could direct them to specific items. For this you decide to show him how to filter records.

There are two basic methods of filtering in order to limit the records looked at:
1 - Filter by form - where you enter select criteria from pull-down text menus.
2 - Filter by selection - where you look and click

The Filter by Selection is by far the easiest and most obvious way to filter because you see the criteria you are selecting for as you select. It also makes it easy to filter by a series of selected criteria in a type of "drill down" selection.

Example of filtering by Selection

Note: to make this example work we added a clothing item.

Step Illustration Description

1 Original starting point

2
We are going to filter for all "Clothing" in the Category field.

Place the cursor in any of the Category fields with "Clothing" and click the funnel with the lightning bolt icon.

3
Now we are down to only "Clothing" category items.

Let's filter for clothing which is described as "Like New"

Again place the cursor in a field with the selection you want and again click the funnel and lightning bolt icon.

4
The results. Now we have all clothing described as "Like New".

Notice that once we started selecting, the plain-funnel icon gets a border.

Use the plain-funnel icon to remove all filters to this point.

5 Once you click the plain-funnel icon you are back to the start.

Special Note: The selection filter used here for a table is also usable in any query, in exactly the same way.

Filter by Form does the same thing a little differently. Filtering by form used to be awkward and I abandoned it because you didn't really see what kinds of selections you are looking for. You simply had to know they were there and you typed them in. However, Access added pulldown lists for each field giving you a selection of items in that column (field). Each entry is shown only once (In SQL this is a "Select Distinct" operation).

Example of the same selection using the Filter By Form method

1
Original starting point.

At this point we click on the funnel-in-front-of-a-form icon to get the screen you see below.

2
Here we have the Filter-By-Form interface.

In those fields in which there are multiple items for the same values you will see pull-downs with a title line already populated (showing a default selection).

Here we are picking "Clothing."

3
We can make several selections before we apply the filter or set of filters.

Here we add in the "Like New" description before we apply the final check.

4 Note that the Item column's (field's) entries are all different. Nothing shows for a default selection.

5
Ready to Apply

We've made all our selection choices and now we need to click on the simple-funnel icon to apply the filter and produce the display we see next.

6 After clicking the simple funnel icon we wind up in the same place as above with the filter by selection method.

7 To remove the filter simply click the simple-funnel icon again and you are back where you started.

8 Special Note: The selection of filter by form as used here for a table is also usable in any query, in exactly the same way.

A second problem is having access to a list of items for sale. The table Merchandise contains items both sold and for sale. It also contains the price he paid for each item (something not all customers should see). You recommend creating a query that shows only the items from the table Merchandise that are currently for sale.

Specifically

1. Open the database GrandmasAttic. This is the database you created for assignment one (1).

Open the table Merchandise

Set a filter so that you display only records sold to customer Charleen (HINT: Charleen is Customer Number 2 and that number is stored in the Merchandise table in the SoldTo field)

Sort, in ascending order, on DateOfSale

Close the table. (IMPORTANT: When you close the table be sure to save your changes so that this can be graded.)

2. Create a query called Inventory (from the same database, GrandmasAttic)

This query should include the following fields from the table Merchandise: Category, Item, Description, SalePrice.

The query should sort in ascending order on the field Category

Select records for items that have not been sold (HINT: The SoldTo field has a value of 0. If SoldTo is greater than zero it is a customer's number and therefore that item has been sold).

Even though you are selecting records based on the SoldTo field your query should not display the SoldTo field.

Questions and answers from the email archives...

Once you have finished both steps above, use file upload (or email as an attachment) the database file GrandmasAttic. This is assignment DB2.

Copyright 1998-2004 by the Curators of the University of Missouri

Example of filtering by Selection Note: to make this example work we added a clothing item.
Step	Illustration	Description
1		Original starting point
2		We are going to filter for all "Clothing" in the Category field. Place the cursor in any of the Category fields with "Clothing" and click the funnel with the lightning bolt icon.
3		Now we are down to only "Clothing" category items. Let's filter for clothing which is described as "Like New" Again place the cursor in a field with the selection you want and again click the funnel and lightning bolt icon.
4		The results. Now we have all clothing described as "Like New". Notice that once we started selecting, the plain-funnel icon gets a border. Use the plain-funnel icon to remove all filters to this point.
5		Once you click the plain-funnel icon you are back to the start.
	Special Note: The selection filter used here for a table is also usable in any query, in exactly the same way.

Example of the same selection using the Filter By Form method
1		Original starting point. At this point we click on the funnel-in-front-of-a-form icon to get the screen you see below.
2		Here we have the Filter-By-Form interface. In those fields in which there are multiple items for the same values you will see pull-downs with a title line already populated (showing a default selection). Here we are picking "Clothing."
3		We can make several selections before we apply the filter or set of filters. Here we add in the "Like New" description before we apply the final check.
4		Note that the Item column's (field's) entries are all different. Nothing shows for a default selection.
5		Ready to Apply We've made all our selection choices and now we need to click on the simple-funnel icon to apply the filter and produce the display we see next.
6		After clicking the simple funnel icon we wind up in the same place as above with the filter by selection method.
7		To remove the filter simply click the simple-funnel icon again and you are back where you started.
8	Special Note: The selection of filter by form as used here for a table is also usable in any query, in exactly the same way.