Microsoft Access

Access 2013 Chapter 3 Creating and Using Queries Last Updated: 3/2/15 Page 1

USING MICROSOFT ACCESS 2013 Guided Project 3-1

Guided Project 3-1 You have built a database to store information about a friend’s music collection. You want to write three queries to help answer some questions about the songs in the database. To ensure consistency, the starting file is provided for you. Use the Simple Query Wizard to create a query, use Design view to edit the query, and add criteria. After saving and testing the query, save a copy of the query to add a parameter. Finally, create a third query that includes summary query capabilities.

Skills Covered in This Project  Create a query using the Simple Query Wizard.

 Edit a query in Design view.

 Add compound criteria to a query.

 Execute a query.

 Save a query.

 Add a parameter to a query.

 Sort query results.

 Edit a query to add an aggregate function.

1. Open the LargerMusicDatabase-03.accdb start file.

2. The file will be renamed automatically to include your name. Change the project file name if

directed to do so by your instructor.

3. If needed, enable content in the security warning.

4. Open the Album table in Datasheet view to view the contents. You should see 27 different records.

5. Click the plus symbol to the left of

AlbumID 6, to see the songs that are on

Maroon 5’s Hands All Over album.

6. Close the table.

7. Create a new query using the Simple

Query Wizard.

a. Click the Query Wizard button

[Create tab, Queries group] to launch

the Query Wizard.

b. Select Simple Query Wizard in the

New Query dialog box.

c. Click the OK button to launch the

Simple Query Wizard (Figure 3-87).

Verify that the Album table appears

in the Tables/Queries box.

d. Select AlbumName in the Available

Fields window.

e. Click the Move Single Field button to

move the field into the Selected Fields

window.

f. Select ArtistName in the Available Fields window.

g. Click the Move Single Field button.

h. Continue moving over the Genre and ReleaseDate fields.

i. Click the Tables/Queries drop-down arrow and select the Songs

table.

j. Select SongTitle in the Available Fields window.

k. Click the Move Single Field button. The completed Selected Fields

window should match Figure 3-88.

l. Click the Next button. The second page of the Simple Query Wizard

displays. Because of the relationship between the two tables, Access

asks you whether you want a detail or summary query.

m. Leave the radio button selected for Detail and click the Next button.

n. Change the title in the Title box to SongsOnAlbums. o. Click Finish to save and run the query. The query results should match those shown in Figure 3-89,

displaying 355 total records.

Step 1

Download start file

Access 2013 Chapter 3 Creating and Using Queries Last Updated: 3/2/15 Page 2

USING MICROSOFT ACCESS 2013 Guided Project 3-1

8. Edit the query in Design view to add criteria to find songs on rock albums released after 1/1/2000.

a. Click the View button [Home tab, Views group] and select the Design View option to switch back

to Design view of the query.

b. Type Rock in the Criteria row of the Genre field. c. Tab out of that field. Notice that Access automatically puts quotation marks around the text

criteria.

d. Click the Run button [Query Tools Design tab, Results group] to run the query. The query should

open in Datasheet view and display the 93 songs that were on rock albums.

e. Switch back to Design view and type >=#1/1/2000# in the Criteria row of the ReleaseDate field. f. Click the Run button [Query Tools Design tab, Results group]. The query should display 30

songs from Maroon 5’s Call and Response: The Remix Album and John Mayer’s Born and

Raised albums.

9. Save a copy of the query.

a. Click the Save As button [File Tab].

b. Select the Save Object As button and then click the Save As button.

c. Replace the suggested object name in the Save ‘SongsOnAlbums’ to box with

SongsOnRockAlbumsAfterJan2000. d. Click OK.

e. Close the query.

10. Edit the SongsOnAlbums query in Design view to add a parameter for the ArtistName field.

a. In the Queries group of the Navigation pane, select the SongsOnAlbums query. Right-click to

open the context menu and select the Design View option to open the query in Design.

b. Type [Show songs by which Artist?] in the Criteria row of the ArtistName field. c. Click the Run button [Query Tools Design tab, Results group] to run the query. The Enter Parameter

Value dialog box displays.

d. Type Maroon 5 and click OK (Figure 3-90). The query should open in Datasheet view and display the 48 songs on albums released by Maroon 5.

11. Edit the query to add sorting.

a. Click the View button [Home tab, Views group] and select

the Design View option to switch back to Design view of

the query.

b. Click in the Sort row of the ReleaseDate field and select

Ascending.

c. Click the Run button [Query Tools Design tab, Results group]

to run the query. The Enter Parameter Value dialog box

displays.

d. Type Maroon 5 and click OK. e. Verify that the results are displayed in ascending order on the ReleaseDate field.

Access 2013 Chapter 3 Creating and Using Queries Last Updated: 3/2/15 Page 3

USING MICROSOFT ACCESS 2013 Guided Project 3-1

12. Save a copy of the query.

a. Click the Save As button [File Tab].

b. Select the Save Object As button and then click the Save As button.

c. Replace the suggested object name in the Save ‘SongsOnAlbums Query’ to box with

SongCountArtistParameter. d. Click OK.

13. Edit the query to add a Total row.

a. In Design view, click the Totals button [Query Tools Design tab, Show/Hide group].

b. Click in the Total row cell for the SongTitle field.

c. Click the drop-down arrow and select Count.

14. Click the Save button to save the query.

15. Click the Run button [Query Tools Design tab, Results group] to run the query.

a. The Enter Parameter Value dialog box displays.

b. Type Maroon 5 and click OK. c. The query should open in Datasheet view and display the CountOfSongs on the 3 albums by

Maroon 5 (Figure 3-91).

d. As desired, extend the width of the CountofSongTitle column and save the changes to query.

16. Close the database.

17. Upload and save your file.

18. Submit project for grading.

Step 2

Upload & Save

Step 3

Grade my Project