8.6 Finding data

Databases allow you to display just part of the data. For example, a school may wish to just display details for the girls, or all the students who travel to school by bus, or those students who speak a language other than English.

You can search on just one request or entry, which is called a single selection search, or you can search on multiple requests or entries, which is called a multiple selection search.

Databases exercise 4

Single selection searches

Skills practised
  • Single data searches
  • Multiple data searches

Let’s display just the Richmond employees.

  1. In Microsoft Access:
    1. Open the EMPLOYEE PAY QUERY, click in one of the RICHMOND values then click on the SELECTION icon in the ribbon and select EQUALS “RICHMOND”.
    2. Just the Richmond employees are found and the number found is displayed in the Status Bar at the base of the screen.
    3. Click on the TOGGLE FILTER icon in the ribbon to return all the records to the list.
  2. In FileMaker Pro:
    1. Click on the FIND button in the Toolbar (or press <Ctrl+F> or <Command+F>).
    2. Enter Richmond in the box under LOCATION and click on the PERFORM FIND in the Toolbar (or press <Return> or <Enter>).
    3. Just the Richmond employees are found and the number found is displayed in the Toolbar.
    4. Click on the SHOW ALL button in the Toolbar to return all the records to the list.
      Note: It is always a good practice to return all the records to the list before you carry out new searches.

Multiple selection searches

Searches can be made on more than one piece of data. For example, in the Pizza Palace database, Joe might want to know how many Delivery staff work at the Keilor shop or how many Cooks work over 35 hours a week.

  1. In Microsoft Access:
    1. Open the EMPLOYEE PAY QUERY then click on the ADVANCED icon in the ribbon and select FILTER BY FORM.
    2. Enter Keilor under LOCATION Delivery under POSITION.
      TS0811.png
    3. Click on the TOGGLE FILTER icon in the ribbon to show the employees. Two should be found.
    4. Click on the ADVANCED icon again and select CLEAR FILTER to return all the records to the list.
    5. You can also do a search in the Query Design when you want to keep the search. You can duplicate the current query or create a new one. By doing this you could have a query for the Richmond employees, another for the Keilor employees, etc. In this case we will use the current query.
    6. Click on the VIEW icon in the ribbon to open the QUERY DESIGN.
    7. In the CRITERIA row enter Cook in the POSITION cell and >35 in the HOURS cell.
      TS0812.png
    8. Run the query and four cooks should be found.
    9. Return to the QUERY DESIGN, remove the entries in the CRITERIA row and run the query to return all the records to the list.
  2. In FileMaker Pro:
    1. Click on the FIND button in the Toolbar (or press <Ctrl+F> or <Command+F>).
    2. Enter Delivery under POSITION and Keilor under LOCATION.
      TS0813.png
    3. Click on the PERFORM FIND button in the Toolbar (or press <Return> or <Enter>) and two employees should be found.
    4. Press <Ctrl+F> or <Command+F> to perform another search. Enter Cook in the POSITION box and >35 in the HOURS box.
      TS0814.png
    5. Press <Return> or <Enter> and four employees should be found.
    6. Click on the SHOW ALL button in the Toolbar to return all the records to the list.