Query Basics in MS Access

A query is a database object that allows you to ask questions about your data and display the results that match your requirements.

Example: Students Table

You can use a query to answer questions such as:

• Which students are enrolled in ADCA?
• Which students have fees greater than ₹5,000?
• How many students are enrolled in each course?
• What is the total fee amount for all students?

• Retrieve data: Display selected fields from a table.
• Filter records: Show records that match specific conditions.
• Sort data: Arrange records in ascending or descending order.
• Calculate values: Perform calculations such as totals and averages.
• Combine tables: Retrieve related information from multiple tables.
• Update data: Modify multiple records using an Update query.
• Delete data: Remove records using a Delete query.

Queries are useful in schools, libraries, offices, businesses, and student management systems.

1. Select Query

Retrieves and displays records that match specified conditions. It is one of the most commonly used query types.

Example: Display all students enrolled in ADCA.

2. Parameter Query

Prompts the user to enter a value when the query runs.

Example: Enter a course name to display students in that course.

3. Update Query

Changes existing values in multiple records that match specified conditions.

Example: Increase the fees for students in a particular course.

4. Delete Query

Deletes records that match specified conditions.

Example: Delete test records that are no longer required.

5. Append Query

Adds records from one data source to an existing table.

6. Make-Table Query

Creates a new table from the results of a query.

7. Crosstab Query

Summarizes data into row and column headings, making it useful for comparing totals across categories.

1. Open your Microsoft Access database.
2. Click the Create tab on the ribbon.
3. Click Query Wizard.
4. Select Simple Query Wizard and click OK.
5. Choose the table or query that contains your data.
6. Select the fields you want to display.
7. Click Next.
8. Choose Detail or Summary if the wizard offers that choice.
9. Enter a name for your query.
10. Choose to open the query to view results and finish the wizard.

Query Design View gives you more control over the fields, sorting, and criteria used in a query.

1. Open your database.
2. Click the Create tab.
3. Select Query Design.
4. Add the table containing your data.
5. Close the Show Table or Add Tables window.
6. Double-click the fields you want to include in the query.
7. Set any sorting options or criteria in the query grid.
8. Click Run (!) to display the results.
9. Save the query with a meaningful name.

Example: Query Design Grid

This grid selects the StudentName, Course, and Fees fields. It sorts names alphabetically and applies criteria to the Course and Fees fields.

Because both criteria are on the same Criteria row, the query returns records where Course is ADCA and Fees is greater than 5000.

Criteria are conditions that determine which records appear in a query's results.

In Access's default ANSI-89 query pattern syntax, an asterisk (*) matches zero or more characters. Database settings and ANSI-92 mode can affect wildcard syntax.

SQL stands for Structured Query Language. It is used to communicate with databases and retrieve or modify data.

In Microsoft Access, you can view or write SQL by opening a query and switching to SQL View.

Example 1: Display All Records

SELECT *
FROM Students;

The asterisk (*) selects all fields from the Students table.

Example 2: Display Selected Fields

SELECT StudentName, Course
FROM Students;

This query displays only the StudentName and Course fields.

Example 3: Filter Records

SELECT *
FROM Students
WHERE Course = "ADCA";

This query returns records whose Course value is ADCA.

Example 4: Sort Records

SELECT StudentName, Fees
FROM Students
ORDER BY Fees DESC;

This query sorts student records by Fees from highest to lowest.

To Run a Query

1. Open the query in Design View or SQL View.
2. Click Run (!) on the ribbon, or use Datasheet View where appropriate.
3. Review the results.

To Save a Query

1. Press Ctrl + S or click Save.
2. Enter a meaningful query name.
3. Click OK.
4. Find the saved query in the Navigation Pane.

Saved queries can be opened again and run whenever you need their results. A saved Select query stores the query definition, not a separate permanent copy of its displayed results.

Create a Students table containing these fields:

• StudentID — AutoNumber
• StudentName — Short Text
• Course — Short Text
• Fees — Currency

Tasks

1. Enter at least ten student records.
2. Create a query to display all student records.
3. Create a query that displays only StudentName and Course.
4. Filter students whose Course is ADCA.
5. Find students whose Fees are greater than 5000.
6. Sort students by StudentName in ascending order.
7. Write a SQL query to display students with fees below 10000.
8. Save your queries with meaningful names.

Goal: Learn to create, run, save, and understand basic queries in Microsoft Access.

1. What is a query in MS Access?
2. Why do we use queries?
3. What is a Select query?
4. What is the purpose of Query Design View?
5. What are query criteria?
6. What does SELECT * mean in SQL?
7. What is the purpose of the WHERE clause?
8. How do you save a query?