How to modify query properties in Microsoft Access

Article ID: 304356 - View products that this article applies to.
This article was previously published under Q304356
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.accdb and .mdb). For a Microsoft Access 2000 version of this article, see 304357
(http://support.microsoft.com/kb/304357/EN-US/ )
.
For a Microsoft Access 97 version of this article, see 304451
(http://support.microsoft.com/kb/304451/ )
.
Expand all | Collapse all

SUMMARY

This article shows you how to modify query properties in Microsoft Access. This information is useful if you want to perform such tasks as:
  • Change the display of fields.
  • Create top value queries.
  • Add an alias for a table.
  • Change field captions and formatting.
Back to the top | Give Feedback

MORE INFORMATION

Query Properties

To view all the query properties, in Design view of any query, right-click in an empty space in the query design window, and then click Properties on the shortcut menu. In some versions of Access, the property sheet will open and default to the "Field List Properties". To display the Query Properties, left-click in an empty space in the query design window.


The following table lists all the query properties and explains what each one is used for.

Collapse this tableExpand this table
Property NameDescription
DescriptionYou can use the Description property to provide information about objects contained in the Database window, as well as about individual table or query fields.
Default ViewYou can use the DefaultView property to specify the opening view of a query.
Output All FieldsYou can use the OutputAllFields property to show all fields in the query's underlying data source and in the field list of a form or report. Setting this property is an easy way to show all fields without having to click the Show box in the query design grid for each field in the query.
Top ValuesYou can use the TopValues property to return a specified number of records or a percentage of records that meet the criteria that you specify. For example, you might want to return the top 10 values or the top 25 percent of all values in a field.
Unique ValuesYou can use the UniqueValues property when you want to omit records that contain duplicate data in the fields displayed in Datasheet view. For example, if a query's output includes more than one field, the combination of values from all fields must be unique for a particular record to be included in the results.
Unique RecordsYou can use the UniqueRecords property to specify whether to return only unique records based on all fields in the underlying data source, not just those fields present in the query itself.
Run PermissionsYou can use the RunPermissions property in a multiuser environment with a secure workgroup to override the existing user permissions. This allows you to view a query or run an append, delete, make-table, or update query that you otherwise wouldn't have permission to run. For example, as a user, you may have read-only permission for queries, while the owner of the queries has read/write permission. If the owner sets the RunPermissions property to specify the owner's permissions, you can run an append query to add records to a table.
Source DatabaseThe SourceDatabase property specifies the external database in which the source tables or queries for a query reside.
Source Connect StrThe SourceConnectStr property specifies the name of the application used to create an external database.
Record LocksYou can use the RecordLocks property to determine how records are locked and what happens when two users try to edit the same record at the same time.
Recordset TypeYou can use the RecordsetType property to specify what kind of recordset is available.
ODBC TimeoutYou can use the ODBCTimeout property to specify the number of seconds Microsoft Access waits before a time-out error occurs when a query is run on an Open Database Connectivity (ODBC) database. By setting this property to zero (0), no time-out will occur.
FilterYou can use the Filter property to specify a subset of records to be displayed when a filter is applied to a form, report, query, or table.
Order ByYou can use the OrderBy property to specify how you want to sort records in a form, query, report, or table.
Max RecordsSpecifies the maximum number of records that will be returned by an ODBC database to an Microsoft Access database (.mdb).
OrientationYou can use the Orientation property to specify or determine the view orientation.
Subdatasheet NameYou can use the SubdatasheetName property to specify or determine the table or query that is bound to the subdatasheet.
Link Child FieldsYou can use the LinkChildFields and LinkMasterFields properties together to specify how Microsoft Access links records in a form or report to records in a subform, subreport, or embedded object, such as a chart. If these properties are set, Microsoft Access automatically updates the related record in the subform when you change to a new record in a main form.
Link Master FieldsYou can use the LinkChildFields and LinkMasterFields properties together to specify how Microsoft Access links records in a form or report to records in a subform, subreport, or embedded object, such as a chart. If these properties are set, Microsoft Access automatically updates the related record in the subform when you change to a new record in a main form.
Subdatasheet HeightYou can use the SubdatasheetHeight property to specify or determine the display height of a subdatasheet when expanded.
Subdatasheet ExpandedYou can use the SubdatasheetExpanded property to specify or determine the saved state of all subdatasheets within a table or query.

Field List Properties

To view the field list properties, in Design view of any query, right-click in any cell in the query design grid, and then click Properties on the shortcut menu.

Collapse this tableExpand this table
Property NameDescription
AliasYou can use the Alias property to specify a custom name for a source table or query when you use the same table or query more than once in the same query.
SourceYou can use the Source property to specify the source connection string and source database for a query's source table or query.


Note The following two properties are not available in Access 2000:
  • Default
  • View Orientation
Note The following two new properties were added in Access 2007 for queries:
  • Filter On Load
  • Order By On Load
For additional information about lookup field properties, click the article number below to view the article in the Microsoft Knowledge Base:
304462
(http://support.microsoft.com/kb/304462/EN-US/ )
ACC2002: How to Add Lookup Fields in a Microsoft Access Table
Back to the top | Give Feedback

REFERENCES

For more information about modifying query properties, click Microsoft Access Help on the Help menu, type change query properties in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

You can also view this information on the World Wide Web at the following Microsoft Web site: For additional information about creating select queries, click the article number below to view the article in the Microsoft Knowledge Base:
304325
(http://support.microsoft.com/kb/304325/EN-US/ )
ACC2002: How to Create a Select Query in Microsoft Access
For additional information about creating parameter queries, click the article number below to view the article in the Microsoft Knowledge Base:
304352
(http://support.microsoft.com/kb/304352/EN-US/ )
ACC2002: How to Create a Parameter Query in Microsoft Access
For additional information about creating crosstab queries , click the article number below to view the article in the Microsoft Knowledge Base:
304349
(http://support.microsoft.com/kb/304349/EN-US/ )
ACC2002: How to Create a Crosstab Query in Microsoft Access
For additional information about converting a select query to an action query, click the article number below to view the article in the Microsoft Knowledge Base:
304354
(http://support.microsoft.com/kb/304354/EN-US/ )
ACC2002: How to Convert a Select Query to an Action Query
You can also view queries for most of the techniques that are described in these articles in the sample file QrySmp00.exe that is in the following article:
207626
(http://support.microsoft.com/kb/207626/EN-US/ )
Access 2000 Sample Queries Available in Download Center
Back to the top | Give Feedback

Properties

Article ID: 304356 - Last Review: November 2, 2010 - Revision: 9.0
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbquery kbhowto KB304356
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top