Call us on 0844 800 7880
MS Access 2013 : Using query criteria

Introduce Me


new mouse hands up2Using query criteria

In addition to using queries to extract specific fields of data from a table, they can also be refined to include selection criteria for each field.

For example, a query could be created to show only the FirstName and LastName fields of a customer contact table, and then further narrowed down to include only those with a LastName beginning with a specific letter.

 

 

Tell Me

Adding selection criteria to a query:

  1. Select the Create ribbon tab.
  2. Click Query Design (within the Queries group).


    2013AccessIntropm Using query criteria01
  3. Select the table to be queried.
  4. Click Add.
  5. Continue to add tables as required.
  6. Click Close.
  7. Double click the fields within each table which are required for the query.
  8. To sort the results by a particular field, click within the Sort row under the chosen field and select the appropriate option from the drop down list.


    2013AccessIntropm Using query criteria02
  9. The Show check box is used to ensure the field appears in the final results. If a field is not selected to be shown, the query will still use any criteria associated with that field to create the results, but the field column itself will not be displayed.


    2013AccessIntropm Using query criteria03
  10. Type into the Criteria row the value upon which the query selection is to be made, then press Enter (the speech marks are automatically added by Access, so do not have to be typed in). The symbol equals "=" is also employed.  In the example below we are looking for a city equal to London.


    2013AccessIntropm Using query criteria04
  11. Execute the query by clicking Run (within the Results group).


    2013AccessIntropm Using query criteria05
  12. This will display only those records that match the selection criteria.

     2013AccessIntropm Using query criteria06

Tell Me More



Wildcards:

If a selection query is to be based on part of a field only, wildcards can be used. These form placeholders for characters, with the symbol * being used to represent multiple characters, and ? to denote a single character.

For example, typing in a criteria of A* will find all the values within that field that begin with A.

Note: Access automatically completes the expression as Like “A*”


Operators:

Operators can also be used for more complex criteria, especially when dealing with numerical and date related fields. For example, to produce a query where the results are all after a specific date e.g. 1st January 1990, the criteria would be typed in as >01/01/1990.

The available operators are:

= Equal to
<> Not equal to
< Less than
<= Less than or equal to
> More than
>= More than or equal to
BETWEEN Between or equal to
IN Finds values included in a list
LIKE Finds matches to a character pattern


Tip

1aSSTipPic


For more information, see the Using multiple query criteria lesson.  Below is an example of looking for employees who joined the organisation before 1st January 1993 and who work in a Sales role - i.e. multiple criteria.

 

2013AccessIntropm Using multiple query criteria04



Help Me

new mouse glasses  lightbulb


Try and follow the steps below to give you a better learning experience:

  1. Make time for your learning on a frequent basis – remember little and often will be most effective.
  2. Think about what time of day will be best for you, and have some time to practice your new found skills.
  3. Speak to your line manager and request some time for learning, after all it will improve your outputs in the long term.
  4. And finally, read your Weekly Tips from Bite Size – learning something new this week could save you time and frustration next week.

Good Luck and Happy Learning!

 

 

 

 
HomeMs access 2013 using query criteria

My Bite Size Login



On-line Courses
Facebook Twitter LinkedIn Google+ YouTube