Lesson 3: Number Ones

In this lesson, you will learn more about queries, including some new terms you haven’t encountered before called wildcards and parameter value queries using a database of UK number 1 hit singles.

Learning objectives

  • Use queries to find specific information
  • Use queries to search for partial data
  • Make multi-purposemulti-use queries
  • Create reports to present query results effectively

Refresh your memory: sorts, filters & queries

Task

Sort & filter

Practice your sorting & filtering skills from last week by finding answers to the following questions:

  1. All songs by All Saints
  2. All songs from 2003 that spent 2 weeks at number 1.
  3. All songs from 2004 with fewer than 2 weeks at number 1.

Queries

Easy, right? Now let’s make some queries. For each of the following questions, you should:

  • Create a new query
  • Name it appropriately (e.g. qry_Beyonce)
  • Type the answer to the questions below to prove you got it right.
  1. All songs from 2003 OR 2004 AND spent 4 or more weeks at number 1.
  2. All songs between number 1000 and 1100 that spent more than 4 weeks at number one.
  3. All songs by Take That that spent fewer than three weeks at number 1.
Find out how

Wildcards

Now for some wildcards (refresh your memory in the presentation above if you need to). Make a new query for each of the following searches.

  1. All songs featuring Beyonce.
  2. All songs with the word love in the title.
  3. All songs featuring Lady Gaga that spent 2-3 weeks at number 1.

Parameter value queries

Make a new set of queries for the following parameter value searches.

  1. Open a window that says ‘Please enter the artist name’ and test it by looking for Westlife and Pink.
  2. Open a window that says ‘Please enter the year the song was released’ and test it by looking for at least three different years.
  3. Open a window that says ‘Please enter the minimum number of weeks the song was at number one’ and put >= in front of your parameter value search string to make it work.

Reports

Sometimes you might be asked to provide the results of a query in a well-formatted, printable form. In Access, these are called reports. Learn how to create one in the tutorial below and put those skills to use in completing the tasks that follow.

  1. Create a report for your Take That fewer than 3 weeks at number one query from earlier.
  2. Create a report for the query of songs that contain the word love in the title from earlier.
  3. Make sure both reports have:
    • A consistent style.
    • This image as the logo in the top left with the title “TES Radio:” followed by a descriptive title for each set of results.
    • All fields wide enough to show all data.
    • Fit on one page wide (landscape).
    • A count of the total number of records visible at the bottom.
    • Your name in the footer.

Get some practice!

Open your Pokemon database from last week. Think up ways of using wildcards and parameter value queries to search this data set.

Consolidation & practice