Omitting Duplicates
Display the query's property sheet to omit duplicates. With no fields selected, click the Properties button in the toolbar. Choose Yes from the Unique Values property.
Parameter Query
A parameter query prompts the user for input whenever it is run, rather than specifying a criterion within the query design.
- Clear all criteria from the query design
- Type a value within brackets in the Criteria field, making sure that it does not match the name fields in the table. Using the same name as another field in the table will not prompt the user for input.
Top-Values Query
Allows the user the show only a specified number of records rather than all records in a query.
- Click Top Values box on toolbar
- Specify number or percentage of records to show
Calculations
Calculations are not just for Excel! They can be performed in Access queries as well. A calculated field is used to calculate values from other fields.
- Enter a name for the calculated field followed by a colon, then the expression in one of the columns in the Field row
- Use the Zoom... command to view the entire expression
- Use brackets around each field name
Calculating Statistics
Statistics in Access are functions like COUNT, SUM, AVG, MAX, MIN, STDEV, VAR, FIRST, and LAST. They are considered aggregate functions, a function that performs some mathematical function against a group of records.
- Include the Total row in the design grid by clicking the Totals button (Sigma) on the toolbar
- Choose the desired statistic from the drop down menu
Crosstab Queries
Crosstab queries are used for summarizing data by calculating a statistic for data that is grouped by two different types of information.
- Choose Crosstab Query Wizard from New Query dialog box
- Select field for row headings
- Select field for column headings
- Select Statistic for calculations
- Run the
Joining tables is used to find records in two tables that have identical values in matching fields. With one of the tables you'd like to include in the query, click the new object button and choose query. Open the new query in design view. Add another table to the query by clicking the show table button, and choose another table from the dialog box. Expand all the fields to view everything. When both tables are in the work space, you can see the line joining the tables at the matching fields.
Drag the desired fields into the grid. Run the query. Results will show records in which the matching field appears in both tables. Records that do not match the query do not show up.
The join property are properties that indicate which records appear in a join; right click the join line and choose properties to change.
If you don't want to include all the possible records in a query, include criteria.
Calculations can be performed in queries. Simple calculations can be made, plus there is an expression builder available.
No comments:
Post a Comment