CS 105 Database

Assignment 02 - Filters (asking questions)

CIT105P Rubrics
Assignment 6-7-8: Database 1, 2, 3
Search Query10
(Any other suggestions?)

Part 1 - Filters (asking questions in the moment)

The best way to get information you need from someone is to ask them a question. Same with a database. You have information entered and stored in your database. You can get a useful look at that information if you have a way to ask questions of your database. That is what a database is about.

For example, when a customer shops in a store, whether in person or (more useful) on line, the store owner(s) would like the ability to quickly review that customer's purchasing habits so he could direct them to specific items. This entails some sort of search by classifying information. In person, using the database interface, filtering records is a quick way of asking questions by selecting for specific data in an open table.

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 and apply it to a form (we get to forms in the next lesson).
2 - Filter by selection - where you look at information in the spreadsheet view and click on it to set specifications for which data to look for.

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 pick it. Filter by selection also makes it easy to filter by a series of selected criteria in a type of "drill down" selection.

Original starting point

We are going to filter for all "Video" in the Tags field.

Place the cursor in any of the Tags field and right click.

filter by selection 3

The popup menus are the product of right-clicking in the field you intend to select from.

Notice the first level choices select for the entire field.

The second level choices select for information in only a part of the field.

Clicking the "Contains" produces a text box in which to enter the search term

Filter 04

Fill in the text box with your search term, in this case "Video"

The "Contains" means it will search for the word anywhere in that field, regardless of case (upper, lower)

Here is our filtered selection. Note the small filter-funnel icon on the right side of the column heading

Now we are down only to tags with "Video" in them

Let's add a filter for "Music of Our Lives" Use the minimal search term needed.

Again place the cursor in a field with the selection you want (Description), do a right click, choose contains and enter "lives"

Our new set of records with the second filter applied.

Note the filter-funnel icons are now showing on the right sides of the column headings for both fields with a filter.

This means that the record set we are viewing is the combined result of all filters.

Now we have only video of "Music of Our Lives"

remove filters

On the Home Tab you will find the "Toggle Filter" button. Click that to clear all filters.

To clear only one filter at a time right-click inside a field and say yes to the "Clear filters from field?" prompt.

Once you click the toggle button you will have the full data display back.

You are ready to go again.


Part 2 - From Filters to Queries (stored, repeatable questions) Graded Item

Some of the questions you answer by filtering the database you will probably ask numerous times. Generally you will want to keep a regular lookout for certain types of information. Instead of always going through the steps for a filter, you can create a stored set of questions so that you can repeat your filter. A stored (and therefore, repeatable) question is called a Query.

create tab and query wizard icon
Click on the "Create" tab, then on the "Query Wizard" icon.
Field offerings

This gives us a field selection. We could just click on the double chevron button and get all the fields, or, similar to right clicking in desired fields when we filtered, we could simply pick the fields we want to show.

Note that we are picking two kinds of fields
1 - those fields we are using to filter for information
2 - those fields we want to show based on the filter(s)

fields selected
Here we've picked the five fields we want to show. Two of them, Tags and Description will be used to filter for (select for) the stored, repeatable question we are creating in this Query.
set to show detail
Set this to show all records selected.
A name for our query

Hitting the "Next" button (in the previous step) leads to this name-setting dialog.

The suggested name was "Inventory Query" which is fine. But we wanted something to tell us what repeatable question this one is so we've added "vid/lives" to the name.

Going with "Open ... to view ..." gets the next view. Just the fields selected but all records displayed.

Next we will set the query conditions.

first view of our new query

Unfortunately, while we can apply filters again at this point, we are not able to store them using filters. So, we need to go to Design Mode.

design mode, blank, at the start

So, we click on the Design Mode Icon in the upper left of the window (in the Home tab).

design mode icon on the home tab

To get this format, ready for our specifications.

The Criteria: line will be our focus.

like any video anywhere on the line

Note that the asterisk is a "wild card" which means "anything here"

Don't bother typing in the full syntax (i.e "Like" or any equal sign) at this point. If you type in just what you are looking for then move your cursor to the next line, Access will fill in the syntax for you.

Here we have a couple of concepts.

Like "*video*"

means we are looking for a match somewhere in the field. That word "Like" and the quote marks were added.

We just type in *video* and Access filled in the Like and the quote marks.

The asterisk is used to indicate "anything here"

An asterisk on each end of our "search term" means to look for "video" anywhere in the field.

An asterisk at the end (video*) would mean to look for "video" at the start of the field (with anything after).

An asterisk at the start (*video) would mean at the end of the field (with anything before)

video only search output

Clicking the Run Icon (in the Design tab) gets this result.

Here is our result from above. Now let's add a second criteria.

looking for video in Tags and lives in Description

Note the "Run" icon in the upper left. It is in the ribbon for the "Design" tab.

Again, we just entered the word lives with an asterisk on either side. Access added in the word Like and the quote marks.

output for video and lives criteria

So now, with two criteria (the same two we used earlier in the filter) we click on the "Run" icon in the upper left and can see the result.

This time, we have our question in a stored format (Query) so that we can repeat it quickly and easily just by clicking on the query listing on the left.

Make sure this query works for you exactly as it works here. This will be part of your grade.
When your database is done (you may also want to do the next lesson, which adds to this database), send the database file as an attachment in email.