Microsoft Access Query Design and Data Management FAQ

Microsoft Access Query Fundamentals

• Query Creation: Queries are built using the Query Wizard, which prompts for necessary information.
• Datasheet Behavior: When a query is run, the datasheet contents are temporary.
• Field Ordering: The display order of fields in a query is determined by their order in the Design Grid.
• Execution: To view query results, you must run the query.
• Field Selection: Double-clicking the field list title bar in the design window will highlight all fields.
• Adding Fields: You can automatically include all table fields by double-clicking the asterisk in the Field List box.

Managing Related Data and Records

• Multi-Table Queries: Data can be extracted from multiple tables in one query, even if the tables are separate.
• Referential Integrity: You cannot delete a record in a primary table if related records exist until you delete the related records first.
• Record Deletion: To delete a record, select it in Datasheet view.

Sorting and Filtering Data

• Sort Keys: The AutoNumber field type may be specified as a sort key.
• Sorting Process: Before clicking a sort button, you must select a column to base the sort on.
• Sort Limits: In Access, you can specify up to 10 fields for sorting query results.
• Saving Sorts: When a query is saved, the defined sort fields are saved with the query.
• Toggle Filter: Use the Toggle Filter button to switch between filtered and non-filtered displays.
• Wildcards: Use conditions to select records matching patterns with wildcards.
• Comparison Operators: In the condition Invoice Amt > 100 and Invoice Amt <> 200, the symbols > and <> are comparison operators.
• OR Operator: Selection criteria placed in different rows in the query design grid indicate an OR operator.
• Filtering Techniques: Filter By Form displays empty fields, while Filter By Selection allows you to select part of a field value to filter records.

Advanced Query Expressions

• Zoom Box: The Zoom Box is a large text box used to enter text, expressions, and values.
• Field Limitations: Lengthy expressions are often too large for the standard Field text box.
• Calculations: Define calculations by typing them directly into a Field text box in the Design Grid.
• Maximum Function: This function determines the highest field value for selected records.
• Relationship Indicators: The fact that you have not enforced referential integrity is not an indicator of a one-to-many relationship.
• Formatting: To set decimal places for a calculated field, right-click the field and select Properties.

Properties.