Here's a step-by-step guide on how to use Totals and Aggregate Query in MS Access:
Open Microsoft Access and create a new Query.
Group By:
Description: Groups records based on selected fields.
How to Apply:
Add fields to the query grid.
Go to the "Design" tab.
Click on "Totals."
Set the desired fields to "Group By."
Count:
Description: Counts the number of records in each group.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "Count" for the desired field.
Example:
Example Query: Count the number of orders for each product.
Application: Set "Product" to "Group By" and "OrderID" to "Count."
Sum:
Description: Calculates the total sum of a numeric field in each group.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "Sum" for the desired field.
Example:
Example Query: Sum the total sales amount for each product.
Application: Set "Product" to "Group By" and "SalesAmount" to "Sum."
Avg:
Description: Calculates the average of a numeric field in each group.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "Avg" for the desired field.
Example:
Example Query: Find the average grade for each student in each subject.
Application: Set "StudentID" and "Subject" to "Group By" and "Grade" to "Avg."
Caption:
Description: Assigns a custom caption to a field.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "Group By" for the desired field.
In the "Field" row, enter a custom caption.
Example:
Example Query: Instead of "Total Sales," display "Total Revenue."
Application: Set "Product" to "Group By" and enter "Total Revenue" in the "Field" row.
Min:
Description: Finds the minimum value of a numeric field in each group.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "Min" for the desired field.
Example:
Example Query: Find the minimum salary for each department.
Application: Set "Department" to "Group By" and "Salary" to "Min."
Max:
Description: Finds the maximum value of a numeric field in each group.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "Max" for the desired field.
Example:
Example Query: Find the maximum stock quantity for each product.
Application: Set "Product" to "Group By" and "StockQuantity" to "Max."
StDev:
Description: Calculates the standard deviation of a numeric field in each group.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "StDev" for the desired field.
Example:
Example Query: Calculate the standard deviation of responses for each question.
Application: Set "QuestionID" to "Group By" and "Response" to "StDev."
Var:
Description: Calculates the variance of a numeric field in each group.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "Var" for the desired field.
Example:
Example Query: Find the variance in scores for each subject.
Application: Set "Subject" to "Group By" and "Score" to "Var."
First:
Description: Displays the first value of a field in each group.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "First" for the desired field.
Example:
Example Query: Display the first contact date for each customer.
Application: Set "CustomerID" to "Group By" and "ContactDate" to "First."
Last:
Description: Displays the last value of a field in each group.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "Last" for the desired field.
Example:
Example Query: Show the last transaction date for each user.
Application: Set "UserID" to "Group By" and "TransactionDate" to "Last."
Expression:
Description: Applies a custom expression to a field.
How to Apply:
Follow steps 1-3.
Set the "Total" row to "Expression" for the desired field.
Enter the custom expression in the "Field" row.
Example:
Example Query: Concatenate two fields or perform custom calculations.
Application: Set "Field1" and "Field2" to "Group By" and "Expression" to "Field1 & Field2."
Where:
Description: Applies a condition to the totals, filtering the results.
How to Apply:
Follow steps 1-3.
Right-click on the "Total" row and choose "Where."
Enter the condition for the filter.
Example:
Example Query: Filter sales data for products with sales greater than a certain threshold.
Application: Set "Product" and "Sales" to "Group By" and use "Where" to filter "Sales > 1000."
Sort:
Description: Sorts the results based on a selected field.
How to Apply:
Go to the "Sort & Filter" group on the "Design" tab.
Choose the field you want to sort by and select "Ascending" or "Descending."
Example:
Example Query: Sort results by total sales in descending order.
Application: After applying totals, go to "Sort & Filter," choose "Total Sales," and select "Descending."
Run the Query:
Description: Executes the query to display the results.
How to Apply:
Click on the "Run" button in the "Results" group on the "Design" tab.
Example: After setting up all your totals and aggregates, running the query will display the summarized data according to your specifications.