Logo
HOW TO
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
 
 
Windows 7

Microsoft Access 2010 : Enhancing the Queries That You Build - Ordering Query Results, Refining a Query by Using Criteria

8/2/2014 9:43:37 PM

Ordering Query Results

When you run a new query, the query output appears in no particular order. Generally, however, you want to order query output. You can do this by using the Sort row of the query design grid.

Order Results of a Query

To order the results of a query, follow these steps:

1.
In Design view, click within the query design grid in the Sort cell of the column you want to sort by (see Figure 1).

Figure 1. Changing the order of query results.

2.
Use the drop-down combo box to select an ascending or descending sort. Ascending or Descending appears in the sort cell for the field, as appropriate.

Sorting by More Than One Field

You might often want to sort query output by more than one field. The columns you want to sort must be placed in order, from left to right, on the query design grid, with the column you want to act as the primary sort on the far left and the secondary, tertiary, and any additional sorts following to the right. If you want the columns to appear in a different order in the query output, you must move them manually in Datasheet view after you run the query.

Refining a Query by Using Criteria

You have learned how to select the fields you want and how to indicate the sort order for query output. One of the important features of queries is the ability to limit output by using selection criteria. Access allows you to combine criteria by using several operators to specify the criteria for multiple fields. Table 1 covers the operators and their meanings.

Table 1. Access Operators
OperatorMeaningExampleResult of Example
=Equal to.=”Sales”Finds only records with ”Sales” as the field value.
<Less than.<100Finds all records with values less than 100 in that field.
<=Less than or equal to.<=100Finds all records with values less than or equal to 100 in that field.
>Greater than.>100Finds all records with values greater than 100 in that field.
>=Greater than or equal to.>=100Finds all records with values greater than or equal to100 in that field.
<>Not equal to.<>”Sales”Finds all records with values other than Sales in the field.
AndBoth conditions must be true.Created by adding criteria on the same line of the query design grid to more than one fieldFinds all records where the conditions in both fields are true.
OrEither condition can be true.”CA” or “NY” or “UT”Finds all records with the value ”CA”, ”NY”, or ”UT” in the field.
LikeCompares a string expression to a pattern.Like”Sales*”Finds all records with the value ”Sales” at the beginning of the field. (The asterisk is a wildcard character.)
BetweenFinds a range of values.Between 5 and 10Finds all records with the values 5–10 (inclusive) in the field.
InSame as Or.In(“CA”,”NY”,”UT”)Finds all records with the value ”CA”, ”NY”, or ”UT” in the field.
NotSame as <>.Not “Sales”Finds all records with values other than Sales in the field.
Is NullFinds nullsIs NullFinds all records where no data has been entered in the field.
Is Not NullFinds all records that are not nullIs Not NullFinds all records where data has been entered into the field.

Criteria entered for two fields on a single line of the query design grid are considered an And condition, which means that both conditions need to be true for the record to appear in the query output. Entries made on separate lines of the query design grid are considered an Or condition, which means that either condition can be true for Access to include the record in the query output. Take a look at the example in Figure 2; this query would output all records in which the Job Title field begins with either Accounting or Purchasing, regardless of the customer ID.

Figure 2. Adding And and Or conditions to a query.

It outputs the records in which the Job Title field begins with Owner only for the customers whose IDs are greater than or equal to 10. Notice that the word Owner is immediately followed by the asterisk. This means that customer would be included in the output. On the other hand, Accounting and Purchasing are both followed by spaces. That means that only entries that begin with Marketing or Owner followed by a space are included in the output.

Working with Dates in Criteria

Access gives you significant power for adding date functions and expressions to query criteria. Using these criteria, you can find all records in a certain month, on a specific weekday, or between two dates. Table 2 lists the date criteria expressions and examples.

Table 2. Date Criteria Expressions
ExpressionMeaningExampleResult
Date()Current dateDate()Records the current date within a field.
Day(Date)The day of a dateDay ([OrderDate])=1Records the order date on the first day of the month.
Month(Date)The month of a dateMonth ([OrderDate])=1Records the order date in January.
Year(Date)The year of a dateYear([OrderDate])=1991Records the order date in 1991.
Weekday(Date)The weekday of a dateWeekday([OrderDate])=2Records the order date on a Monday.
Between Date And DateA range of datesBetween #1/1/95# and #12/31/95#Finds all records in 1995.
DatePart(Interval, Date)A specific part of a dateDatePart (“q”,[OrderDate])=2Finds all records in the second quarter.

The Weekday(Date, [FirstDayOfWeek]) function works based on your locale and how your system defines the first day of the week. Weekday() used without the optional FirstDayOfWeek argument defaults to vbSunday as the first day. A value of 0 defaults FirstDayOfWeek to the system definition. Other values can be set, too.

Figure 3 illustrates the use of a date function. Notice that DatePart(“q”,[Order Date]) is entered as the expression, and the value 2 is entered for the criterion. Year([Order Date)] is entered as another expression, with the number 2006 as the criterion. Therefore, this query outputs all records in which the order date is in the second quarter of 2006.

Figure 3. Using the DatePart() and Year() functions in a query.
Other -----------------
- Microsoft Access 2010 : Enhancing the Queries That You Build - Everything You Need to Know About Query Basics
- Using the Windows 7 Libraries : USING THE EXPLORER BROWSER CONTROL (part 2)
- Using the Windows 7 Libraries : USING THE EXPLORER BROWSER CONTROL (part 1) - Adding the Explorer Browser to Your Toolbox , Configuring the Explorer Browser Example
- Using the Windows 7 Libraries : CONSIDERING USER-DEFINED COLLECTIONS
- Using the Windows 7 Libraries : USING NON-FILESYSTEM CONTAINERS
- Using the Windows 7 Libraries : WORKING WITH KNOWN FOLDERS
- Conducting Research in OneNote 2010 : Translating Text
- Conducting Research in OneNote 2010 : Researching a Topic, Customizing the Research Task Pane
- Conducting Research in OneNote 2010 : Handling the Research Task Pane
- Microsoft Excel 2010 : Protecting and Securing a Workbook - Setting Macro Security Options
 
 
REVIEW
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
 
VIDEO TUTORIAL
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
 
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
Top 10
- Microsoft Excel : How to Use the VLookUp Function
- Fix and Tweak Graphics and Video (part 3) : How to Fix : My Screen Is Sluggish - Adjust Hardware Acceleration
- Fix and Tweak Graphics and Video (part 2) : How to Fix : Text on My Screen Is Too Small
- Fix and Tweak Graphics and Video (part 1) : How to Fix : Adjust the Resolution
- Windows Phone 8 Apps : Camera (part 4) - Adjusting Video Settings, Using the Video Light
- Windows Phone 8 Apps : Camera (part 3) - Using the Front Camera, Activating Video Mode
- Windows Phone 8 Apps : Camera (part 2) - Controlling the Camera’s Flash, Changing the Camera’s Behavior with Lenses
- Windows Phone 8 Apps : Camera (part 1) - Adjusting Photo Settings
- MDT's Client Wizard : Package Properties
- MDT's Client Wizard : Driver Properties
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro