Working with databases/Database queries/Advanced queries

Working with Databases

Basic skills applicable to planning, creating and using an Access database.
An OP Online course from the National Certificate in Computing (Level 2) programme.

About this course  |  Getting started  |  Course schedule  |  Assessment overview  |  Help

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.

Activity

Open the QRY_Paperback query and sort the query by Year Published, oldest to newest: 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:

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.

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

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: You are to: Create appropriate fields in a table called Student results. Please ensure that you apply correct data-types, formatting and field properties Create a printout of the table, sorted alphabetically by student surname Create a printout of the table sorted with the high scores for all courses and assessments at the top Create a query showing only those students who passed the exam 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.

|