Microsoft Access 2B: Creating Queries: Single and Multiple Criteria and Sorting

Queries:

• Queries are used to pull out certain records eg. all the clients in a particular city, all clients who have purchased an item in the last 6 months etc.

• A single field query may be used one field eg. retrieve all records where the name of the city in the city field is Vancouver
  • within a single field query you may also indicate an and statement (eg. Vancouver and Victoria)

• A multiple field query may use more than one field eg. retrieve all records where the city is Vancouver and the client has purchased something in the last 30 days

Combining Queries With Edit / Replace:

You may use a query to narrow down your data so that the Edit / Replace can be used only on those records selected.

Example: if you wanted to change the area code in the telephone number field for all those cities in a particular area you may use a query to select the cities, then do the Edit / Replace instead of changing each record individually.

Saving Common Queries:

Sometimes you need to look at the same queries all the time, like when looking for money that hasn't been paid by customers within the companies 30 day period.

In Microsoft Access you can save these queries and each time you run them any new information will be included in the new query.

Creating Queries:

To make a query from scratch you would have to know how to construct databases, (which you are doing in this course), however if someone else is using the database but doesn't know how to create a query, you can show them how to open a query which has already been constructed and saved.

 

Example Queries in Different Types of Field Types:

Date Field:

• all records for a particular year
• all records more than 30 days from today
• all records on a particular day last year

Text Field:

• all records where the name starts with an A
• all records where the city is Vancouver

Currency Field:

• all records over a certain amount $10
• all records where the amount is 0

Sorting Queries:

• Fields may also be sorted alphabetically in a query, (eg. all students sorted alphabetically by last name).

This website is copyright protected see contact page for details.