Working with databases/Database queries/Advanced queries

From WikiEducator
Jump to: navigation, search
OtagoPoly Logo S.png




Sorting a Query

Like a table, a query can be organised to display information in ascending order (A ⇒ Z) or descending order (Z ⇒ A ) order.



OP icon activity.gif

Activity

  1. Open the QRY_Paperback query and sort the query by Year Published, oldest to newest:

    Access-criteriasort2.png

  2. Create queries using what you have learned so far:
Mr Mann would like to add a query to his bookshop database. This time he wants a query that displays only hard-covered books. He would like the Title, Year Published, Author , and Paperback fields included.
Mr Mann has also decided that he’d like a third query from you. This time he would like a query that shows premium books with a retail price over $75.00. He would also like to see the Title, Author and ISBN number in this query and to have the query sorted by price, largest to smallest.

If you have any problems with this activity, please review the previous sections of the unit.




Multiple Criteria

It’s possible to have a query with more than one criteria. For example:

Access-multicriteria1.png

This query identifies books that are hard-covers, and that are published before 1950. Note that Access automatically reformats your date query by putting hash marks around the date.

Note also the Show checkbox: if you un-tick this, the field will not be displayed in the query, although it will still be used in running the query.



Web-icon.png

Extra resources

Please note: the following tutorial will open in a new window/tab. When you have finished the tutorial, simply close the window/tab and you'll return to this page.

You might like to check out the following online tutorial for more information about advanced queries:

  • Using Queries to Make Data Meaningful - Part 2




Evaluating the database

When evaluating a database we need to keep the following in mind:

Accuracy
Do the queries contain the right fields? Have we used the correct criteria? Have we used the appropriate data types? Information is entered accurately?
Readability
Have we used appropriate and recognisable field names?
Presentation
Information is presented in a clear and legible format especially if creating forms and reports. Use plain English and not jargon.
Data Integrity
Information must be entered in a consistent way and be accurate. (Phone numbers entered in the same format, consistent use of abbreviations – eg: St, Street, but not both.)

Summary

You have covered:

  • Creating a simple query
  • Editing your Query – adding Criteria
  • Using the Query Design View.
  • Adding a Criteria
    • CriteriaforNumbers
    • CriteriaforText
  • Sorting a Query
  • Evaluating the database



OP icon activity.gif

Activity

The following practice assessment will consolidate all of your learning so far. You must complete this exercise before attempting the assessment for unit standard 2786.

Please read the instructions carefully and review anything you are not sure of.

You are to create a new database to record student marks for coursework and assessments. The following table of information is provided:

Access-studentmarks.png

You are to:

  1. Create appropriate fields in a table called Student results. Please ensure that you apply correct data-types, formatting and field properties
  2. Create a printout of the table, sorted alphabetically by student surname
  3. Create a printout of the table sorted with the high scores for all courses and assessments at the top
  4. Create a query showing only those students who passed the exam
  5. Create a query showing only students who have passed the whole course. A pass is considered the following:
    • More than 60% in the course and
    • More than 50% in the first assessment and
    • More than 50% in the second assessment and
    • More than 65% in the final exam

Your query for this will have four criteria.