Lab 8: Introduction to MS Access

A little heads up - This lab is pretty simple. :) 

Initial set up

1. You have a file, 'moviesDB.mdb' in your ICON. Download that. Double-click to open that.
2. This will be your starting point.  

General info about  MS Access
To view tables

1. Double-click on the 'Director:Table' to view the tables and values associated with it. You'll have something, that looks like this:

Switch to 'Design View'

1. Right-click on the table, and click 'Design view' as shown below

View relationship between tables

1. Select Database Tools -> Relationship. Maximize the smaller tables to view the relationships well.
2. Learn and understand: How director and movies table are related? What is the primary key? How is it represented there? What does the connection arrow signify? 

You assignment starts here..

What is a query?
 Queries are used to view a subset of your data or to answer questions about your data.  

Query: Which movies have titles that start with the letter R?  

Which table and data do you need for this query?

Table: Movies
Column: Title - From title select movies which start with R.

Create a query

• Click Create tab -> Query Design (on the ribbon) -> Movies (Inside Show table) -> Add. Follow the picture below. Here, you select the tables that is required to implement the query. Once you have selected the tables you want, close the 'Show table' dialog box.

• Select the column you want as shown below

• Let's assume that we want the Movie 'Titles' column sorted in Ascending order. Therefore, we specify "Ascending" in "Sort:" row (Similar to Field - select from the drop-down list).
•  Make sure that "Show" check box is checked.
• Since we want all movies that start with letter R, we will use R* in the 'Criteria' column.
• '*'(called as 'asterisk') is a wildcard character which represents any combination of letters and numbers. You use it when you know part of a value, and the rest of the data — such as the length or pattern of the value — isn't important.
• You can use the Like operator to find values in a field that match the pattern you specify. Here, the pattern is 'R*" i.e any movie starting with the letter R.
• Save your query: After you write your criteria, press 'ctrl' and 's' keys to save the query. Give it a proper name. After you save, your query will appear on the Left side pane. You can double-click it to see the tables you have generated after running the query. 

• If you want to make changes to your query, you have to switch to design view. To do that, right-click on your query (which is on the left side pane), and click 'Design View' from there. 
• You'll get a page, which looks similar to this. 

• In this way, you'll create the following queries:

1. Which movies were released before 1980, ordered by year of release? 
2. When were each of the Psycho movies released? (Remember: You should have movie title along with release year)
3. Which directors have last names that start with the letter S? (Remember: You should have Director's Last name (1st preference - Ascending order), First Name (2nd preference - Ascending order))
4. Which movies were directed by directors who were born after 1955?  (Remember: This query involves combination of both the tables. You should have Director's Last name (1st preference - Ascending order), First Name (2nd preference - Ascending order) and Birth Year along with Movie Title (Ascending order).)

• Some reference links:
• Like condition - http://www.techonthenet.com/access/queries/like.php
• Query example - http://www.baycongroup.com/access2007/05_access.html