MS Access Notes - Queries

Objectives: MS Access Notes - Queries

MS Access Notes - Queries

Microsoft Access Components - Queries

1. What is a Query?

A Query in MS Access is used to search, filter, sort, and analyze data from one or more tables. Queries help you get exactly the information you need without manually scanning tables.

Key Points:

  • Queries do not store data (they just display results).
  • They can pull data from single or multiple tables.
  • Can perform calculations like totals or averages.
  • Can filter data using criteria (e.g., only students in Form 1).

2. Types of Queries in MS Access

  • Select Query: Displays data that matches criteria.
  • Action Query: Modifies data (Append, Update, Delete, Make Table).
  • Parameter Query: Prompts user to enter criteria when running.
  • Aggregate Query: Performs calculations like SUM, COUNT, AVG.
  • Cross Tab Query: Summarizes data in rows and columns.

3. Real-Life Example

Using the Students table from Tables notes, we want to find:
  • All students in Form 1.
  • Students born after 2008-01-01.
  • Total number of students in each class.

4. Practical Steps to Create a Query

Step 1: Open Queries

In your database, go to Create > Query Design.

Step 2: Add Tables

Select the tables you want to use (e.g., Students) and click Add, then close the dialog.

Step 3: Select Fields

Double-click the fields you want to include in your query. Example:

  • StudentID
  • FirstName
  • LastName
  • Class

Step 4: Set Criteria

Use the Criteria row to filter results:

  • For students in Form 1: Type Form 1 under the Class column.
  • For students born after 2008-01-01: Under DateOfBirth, type >#2008-01-01#

Step 5: Run the Query

Click the Run button (red exclamation mark) to see the results.

Step 6: Save the Query

Click File > Save, name it Form1_Students, and click OK.

5. Example: Select Query SQL

SELECT StudentID, FirstName, LastName, Class
FROM Students
WHERE Class = 'Form 1';

This SQL does exactly what the query does in Design View.

6. Action Query Example

Suppose we want to increase student IDs by 100 (Update Query):

UPDATE Students
SET StudentID = StudentID + 100;

Be careful: Action queries change your data permanently. Always back up your database.

7. Parameter Query Example

We want to search students dynamically:

SELECT StudentID, FirstName, LastName, Class
FROM Students
WHERE Class = [Enter the class:];

When the query runs, Access will ask the user to Enter the class:.

8. Aggregate Query Example

To count the number of students per class:

SELECT Class, COUNT(StudentID) AS TotalStudents
FROM Students
GROUP BY Class;

This gives a summary of students in each class.

9. Practical Tips

  • Queries can be based on multiple tables using relationships.
  • Always test criteria to ensure correct results.
  • Use meaningful query names for easy reference.
  • Queries can be used as a source for forms and reports.
  • Action queries should be used carefully with backups.

10. Real-Life Usage Scenario

In a small library:

  • Find all books in the "Science" category.
  • Calculate total number of books available per category.
  • Update quantity after borrowing or returning books.

Queries make these tasks faster and more accurate than manual checking.

Reference Book: N/A

Author name: SIR H.A.Mwala Work email: biasharaboraofficials@gmail.com
#MWALA_LEARN Powered by MwalaJS #https://mwalajs.biasharabora.com
#https://educenter.biasharabora.com

:: 1::

⬅ ➡