Filtering Records in MS Access

A filter temporarily displays only the records that satisfy a condition. Records that do not match the condition are hidden from the current view.

Example: Students Table

If you filter the Course field to show only ADCA students, Access displays Amit and Rahul. The other records are hidden until the filter is removed or changed.

1. Filter by Selection

This filter displays records that match a selected value or condition.

Example: Select ADCA in the Course field and apply a filter to show records with that course.

2. Filter by Form

Filter by Form lets you enter criteria into fields in a blank form-like grid to find matching records.

Example: Enter ADCA in the Course field to display ADCA students.

3. Advanced Filter/Sort

This feature allows you to create more detailed filtering conditions and combine criteria across multiple fields.

Example: Show students whose course is ADCA and whose fees are greater than 5000.

4. Filter Using a Search Box

Some Access tables and forms provide a search box in the navigation controls. You can enter text to locate records containing matching information.

Follow these steps to filter records by a selected value:

1. Open your database in MS Access.
2. Open the required table in Datasheet View.
3. Find the field containing the value you want to filter.
4. Click a cell containing that value.
5. Open the Home tab.
6. In the Sort & Filter group, choose Selection.
7. Select the appropriate filtering option.

Use Filter by Form when you want to specify criteria directly.

1. Open the table or form.
2. Go to the Home tab.
3. In the Sort & Filter group, click Advanced.
4. Select Filter by Form.
5. Enter a value in the field you want to filter.
6. Click Toggle Filter to display matching records.

Example

Enter ADCA in the Course field. After applying the filter, Access displays only the records matching that course.

You can enter criteria in more than one field to narrow the results further. The available controls may vary slightly depending on your Access version.

You can use comparison operators to filter numeric, date, and other supported fields.

For example, filtering the Fees field using >5000 displays students whose fees are greater than 5000.

You can combine criteria to find specific records. For example, you may want to display ADCA students whose fees are greater than 5000.

Using AND

When criteria are combined with AND, a record must satisfy both conditions.

Course = "ADCA"
Fees > 5000

When these criteria are entered in the same criteria row of an Access query, both conditions must be true.

Using OR

OR allows a record to match either condition.

Course = "ADCA"
OR Course = "Tally Prime"

In a query design grid, putting criteria on separate Criteria and Or rows is a common way to specify OR conditions.

After filtering records, you can remove the filter to display all records again.

1. Open the filtered table or form.
2. Go to the Home tab.
3. Find the Sort & Filter group.
4. Click Toggle Filter or use the available Clear Filter command.

Access will display the records again, provided no other active filter is restricting the results.

Create a Students table with the following fields:

• StudentID — AutoNumber
• StudentName — Short Text
• Course — Short Text
• Fees — Currency
• AdmissionDate — Date/Time

Tasks

1. Enter at least ten student records.
2. Filter the table to show only ADCA students.
3. Filter students whose fees are greater than 5000.
4. Find students whose course is not Tally Prime.
5. Use Filter by Form to search for a particular course.
6. Create a query to display ADCA students with fees greater than 5000.
7. Remove the filter and display all records again.

Goal: Practice filtering records using values and conditions.

1. What is filtering in MS Access?
2. Does filtering delete records from a table?
3. What is Filter by Selection?
4. What is Filter by Form?
5. Which operator means greater than?
6. What is the difference between AND and OR criteria?
7. How can you remove an active filter?