An Access lab! Surprise! This one though, you won't have to enter in data. I hope you've all mastered designing tables and entering data into tables. If not, please master that before starting the homework (due April 21st).
Let's continue from where we left off last week, with our database of actors and movies. Go ahead and download the sample database that I've made for you. Even if you still have your database from last week, I recommend that you start from that one. Remember to save it (right click, save as/save target as) somewhere, and remember where you've saved it. Now, open it up in Access.
Before we continue, we need to establish the relationships between our tables.
For review, just remember that we have a movies table which holds release date, movie title, and an ID representing the person who directed the movie. Then, we have a people table which holds information about each person (their ID, which is generated automatically by Access, and their actual name). The actors table then keeps a big list of which people were actors in which movies. A person can appear in many different films, and can also be the director of a movie that they're an actor in.
Go to the database tools tab, and click "relationships." Cause all 4 tables to appear.
Now that Access knows how all the information is related, we can create some queries to help view the information.
Let's start off by creating a simple query. We would like to simply see that a movie has a certain director, instead of seeing that director's id. Using the simple query wizard, let's do just that. Specifically, we would like to create a query which shows from the "movies" table, the title, releasedate, and revenue. From the people table, we would like to just show the "personname." Name your query "movie_info_with_director_name". Finish the wizard and view the query. Does it show what you want it to show?
We've only seen queries that list data or filter data. Queries can also compute values. Let's see how much each director made on average across all of the movies he/she has directed. Using the simple query wizard, we would like to show from the "movie_info_with_director_name" query the following fields: revenue, directorname. However, before finishing the wizard, when you get to the step that asks whether you'd like the query to be a detail query or a summary query, click the summary option. Then, click the summary options button. Access is telling us what we can summarize about our fields. What can we calculate about the records we have? Which fields can we look at?
For now, let's click the check boxes to calculate the average revenue, total revenue (sum), as well as the minimum and maximum revenue. We will ignore the box saying "Count records in movies" as we do not care how many records we have listed in movies (i.e., how many movies we have listed). Then, let's click OK. Click next and name your query, "money". Make sure the "Open query to view information" option is clicked. Click Finish, then adjust the width's of the fields so we can see all of the data.
Our query is pretty useful, but it might be improved to help someone new use it. For example, the query says "personname" but really, it might be better to say, "Director". Also, instead of "Avg of revenue" we might prefer to call it, "Average Revenue".
Open that query in design view and take a look at how the query is designed. For example, we see that the personname has "group by" (meaning that we're grouping our data based on director's names) and that the sum, minimum, maximum, and average fields are being computed from the revenue field. If we look in the "field" section we see a pattern. The fields that were computed say something like "Min of revenue: revenue" but the personname field, which wasn't computed, just says, "personname."
If we want to rename how a field is displayed in a query, we add or modify the part before the colon. So, where it says "personname" let's change that to be "Director: personname". Access will still pull the information from the personname field, but it will pretend that the field is called "Director." Let's also rename the calculated fields to instead display "Total Revenue", "Average Revenue", "Minimum Revenue" and "Maximum Revenue." To do this, let's for example change it from "Min of revenue: revenue" to instead say, "Minimum Revenue: revenue". Rerun / reopen the query and make sure that things have changed. Looking at the query, it's now easier to see exactly what we're looking at.
We might also want a big listing of which actors were in which movies. Using the simple query wizard, let's make a new query. We would like to show the movie title and the actors in those movies. For starters, let's first make the title field from the movies table one of our selected fields.
Now, we have to think about how we want to tell Access that it should show all the actors. If we simply added the personname field from the people table, Access might get confused and think that we meant a director. This is to be expected, because there is a direct relationship link (representing the director of each movie) between the people table and the movie table. We have to coax Access to instead use the actors table, even though we don't want to show any information from the Access table.
Since we already have movies.title added as a selected field, let's also add both fields from the actors tables. By doing this, we're helping Access to know that we want a listing of actors. Then, let's add the personname field from the people table. By adding all 4 of these fields, instead of just 2, we're telling Access how it should decide which people names to display. In query #1, we wanted just those people were directors. Now, we just want those who were directors.
We just want a big listing of actors, so this is a detail query. Call the query, "actors_listing" and then look at the results of running the query.
The query is working like we want it to. It shows the name of each movie and also, the actors who were in that movie. More importantly, it is NOT showing the directors of the movies. We can still improve this query though! Let's get rid of those useless fields that we don't care about (e.g., the actors_title field, and the actorid field).
Go to the design view of the query. Notice the checkboxes in the row labelled "Show:". We need those fields to be in our query, so that Access can make the link between the actor names and their movies, but we do not want them to appear. So, uncheck the boxes for the 2 fields we mentioned we don't want to see. Look at your query in design view again, to see the changes. Does it look better? Are the results still correct?
Let's rework the query a little more to make it easier to understand. Just like we did in query #2, make it so that instead of saying "movies_title" and "personname" the query shows "Film Title" and "Actor/Actress". View the query to make sure everything is still correct.
If we had a huge database, we might want to just view the actors/actresses in a specific movie. Open up your actors_listing query, if it isn't open already. Go to design view. Currently, the query shows ALL actors. Let's instead filter based on the title of the film.
On the criteria section for the title field in the query, let's add a criteria. Specifically, let's pretend we only want to show actors from "Mrs. Doubtfire". So, in the criteria, we will set the criteria to be: ="Mrs. Doubtfire"
Take a look at the query results again to make sure it worked. It would be a big pain though, if we wanted to have a query for each movie so that we could quickly see which actors were in a specific film. Right now, there are only 4 movies so we would have to have 4 movies. But, as we added a new movie, it would be painful to add a new query each time. Instead, we can kind of dynamically generate a query. In other words, instead of setting the query to ONLY show those actors who were in Mrs. Doubtfire, we could ask the user to tell Access what movie's actors they want to see.
In the design view of our query, let's replace the criteria of: ="Mrs. Doubtfire" to instead be like: =[Which movie's actors would you like to see?]
That criteria is now in a special format. Previously, we had the criteria in quotes, which just meant that Access should only show actors who were in a specific movie (e.g., "Mrs. Doubtfire"). We had to put the movie name in quotes so that Access knew it wasn't a special value or function. Instead of quotes, we can use brackets ([ and ]). The words inside the bracket are what the user will see/be asked. The user then will get to type in a response and that response will be used in our criteria. So, you could read the criteria of: =[Which movie's actors would you like to see?] as "Show only those movies whose title is equal to a value the user gave."
Rerun your query and make sure it works for a few films. Hit F5 to cause the query to rerun, so that it'll ask the user for another movie title. Remember, you'll have to type in the movie title exactly. What happens if you type in the name of a movie that doesn't exist?
When you're done with all 3 queries, call me over so I can take a look at it!
While you're waiting, think about how we might make a query to show the movies that a specific actor appeared in.
Thank you and have a good day!