1.
Select Queries
Description:
A Select Query is used to retrieve data from one or more tables or queries and display it as a
result. This type of query allows you to specify which fields to display, apply criteria to filter
records, and perform sorting. It's the most commonly used query type in MS Access.
Features:
Allows the selection of fields and records.
Supports sorting and filtering of data.
Can combine data from multiple tables using joins.
Results can be used to create reports or forms.
Use Cases:
Displaying customer records.
Searching for specific products in an inventory.
Filtering employee data by department.
2. Action Queries
Action Queries modify the data in the database rather than just display it. There are four main
types of action queries:
2.1. Update Queries
Update queries allow you to modify existing records in one or more tables. You can update one
or more fields in a table based on specific criteria.
Features:
Used to change data in existing records.
Can update one or more fields in a table at once.
Can apply criteria to select which records to update.
Use Cases:p
Changing the price of a product across multiple records.
Correcting spelling errors in customer names.
2.2. Append Queries
Append queries add records to an existing table. You can add data from one table or query into
another.
Features:
Adds new records to a table.
Data can come from another table or query.
Use Cases:
Importing data from another database or table.
Adding new records from an external source.
2.3. Delete Queries
Delete queries remove records from a table based on specified criteria. Unlike a Select Query,
a Delete Query actually deletes data from the database.
Features:
Removes records based on criteria.
You can delete from one or more tables (if they are related).
Use Cases:
Deleting records that meet certain conditions, such as outdated or invalid data.
2.4. Make-Table Queries
Make-Table queries create a new table based on the results of a query. This type of query is
useful for creating temporary tables or exporting query results.
Features:
Creates a new table.
Allows you to store query results in a new table.
Use Cases:
Exporting filtered or summarized data to a new table.
Creating a backup of a subset of records for reporting purposes.
3. Parameter Queries
Description:
A Parameter Query is a query that prompts the user for input before running the query. The
input is then used to filter the results dynamically, making the query more flexible and
interactive.
Features:
Prompts the user for input (e.g., a date, name, or category).
The query results are filtered based on the user’s input.
Use Cases:
Allowing users to specify a date range or search criteria.
Creating reports where the user can define the parameters (e.g., customer region or order
status).
4. CrossTab Queries
Description:
A CrossTab Query is used to summarize and analyze data in a matrix format, where data is
organized with row and column headings. This type of query is useful for summarizing large
datasets and performing data analysis.
Features:
Transforms rows of data into columns for comparison.
Summarizes data, such as calculating totals or averages.
Displays results in a pivot-table-like format.
Use Cases:
Summarizing sales data by month and product category.
Analyzing survey responses by category.
5. Union Queries
Description:
A Union Query combines the results of two or more SELECT queries into a single result set.
Each SELECT query in a Union must have the same number of fields and corresponding data
types.
Features:
Combines multiple result sets into one.
Allows for data from different tables to be combined into a single view.
Use Cases:
Merging customer data from multiple regions.
Combining order information from different periods or sources.