1. Get to the "Project Manager window. It has 3 areas: Database, Tasks, and Forms (Figure 1). Single click on "Queries", in the Database section. Notice there are 3 options in the Task section: "Create Query in Design View...", "Use Wizard to Create Query...", and "Create Query in SQL View..."
Figure 1. Project Manager window
2. Click on "Create Query in Design View..." A message box will appear with a list of the tables you have in your database. You will be required to click on the tables you want to use for your query and click on the "Add" button. In our case there is only a single table - Albums. So click on it and click on "Add", then click on "Close" (if necessary. Figure shows the screen you should see.
Figure 2. Showing the "Add Tables" dialog box
3. As indicated in Figure 3,There are two areas in this screen. The top area lists the tables that were selected and the bottom is where we will build the query. Notice the down arrow next to "Field". Clicking on it will display a menu with all of the fields listed. Click on it now. Each field is preceded by the table name. Remember we could have multiple tables open and some could have the same field names. Click on "Albums.Genre". Then click on the box next to "Sort" and click on the down arrow and click on "ascending". The "Visible" box should be checked (if not check it).
Figure 3. Query design screen
4. Click on the box next to "Genre"; click the down arrow; select "Albums.Condition"; click on the Visible box (it might take 2 clicks). Click the box next to "Condition"; click the down arrow and select Artist and click the "Visible" box. Do the same for "Year Purchased". Figure 4 displays the result you should see.
Figure 4. Query design after selections
We are finished with building the query. Now what have we asked the program to do? First, because of all of the "Visible" boxes being checked, all of the fields will be displayed; second we have indicated that we want it "Sorted" by genre in ascending order.
The last thing is to execute (run) the query. To do this click on the "Run Query"
button.
The results should be the same as those in Figure 5, below.
Figure 5. Query results
5. Now lets modify the query. This time we will sort by artist, and display only the albums in Average condition. Figure 6 shows the query design and listing results.
Query results for sort and display of Average condition only
6. Your turn - sort by "Year Purchased"; list only the Alternative Genre; and only display the albums in Good condition. Figure 7 (link)shows the result
7. Now we want all of the albums that were purchased 2005 andearlier in ascending order. The first thing is to clear (delete) any current Criterion then under "Year Purchased", in the sort row indicate "ascending". Then in the Criterion box (for Year Purchased) type <=2005. Figure 8 shows what you should have.
Figure 8. Results of <=2005 Criterion.
This means "list all of the records that were purchased in 2005 or earlier. How would you indicate 2005 or later? (Click here for the answer)
Now it is your turn-- the following sequence parallels the steps above however this time you need to document your work by creating a screen print after each step. You will paste the screen print into a Writer document and put an annotation below the graphic that summarizes the process (in 1 sentence) by which you created the results displayed in the graphic (similar to the presentation above).
1. Make sure a Writer document is open and ready. After opening Base, click on File then click on New and finally Writer.
2. Open ch05_case_baseball.ods and register it. ScreenPrint your results and paste it into the document. Create a short annotation at the bottom of the graphic. What is the table name?
3. Create a query. Add the "Tryouts" table. Display all fields. Last Name should be in the first column and the First Name in the second column; the remainder of the fields can be in any order. Sort on Last Name. What is the name of the player who is last in the list? ScreenPrint your results and paste it into the document.
4. Create another query. Sort by Total score and display only those players that are 10 years old. Which player has the highest score and which player has the lowest score? ScreenPrint your results and paste it into the document.
5. Create a query that displays only those players that are "switch hitters" (in batting), sorted by Last Name. How many "switch hitters" are there? ScreenPrint your results and paste it into the document.