Methods Of Filtering A Database In Ms Access 2013 And 2016 – Part 2

To filter a database is to retrieve records that match your initially specified criteria. This means that before MS Access returns filter results, you must first provide some criteria with which it will base its filter operation. 
methods of filtering a database in ms access
There are four main methods to filter a database in Microsoft Access:

1. Filter by form
2. Filter by selection
3. Filter by excluding selection
4. Advanced filter/Sort

This is the part 2 of chapter 4 of the MS Access tutorials. You are advised to study part 1 of chapter 4: INTRODUCTION TO QUERIES IN MICROSOFT ACCESS 2013 AND 2016

You can take a look at the previously treated chapters in this MS Access tutorial. Also see the full tutorial course content of this Microsoft Access Tutorial Series.

FILTER BY FORM: 

This is the first method of filtering database. This filter method creates a lookup column in each field where the criteria can be selected. It then retrieves the records that match the specified criteria.

To filter a database by form:

1. Open the database table in datasheet view.

2. Click the Home tab. Click the drop down arrow beside the Advanced command in the Sort & Filter group and select Filter by Form as shown below.
Select filter by form

3. Access then opens up a blank datasheet with empty fields below the column title and then assigns a lookup button to each field.

4. Select your criteria from the lookup button assigned to each field. You can also type it directly in that field. For example, in my case, I want my filter query to return student(s) whose name(s) is Joseph. So I will type “Joseph” (in quotes) under the FIRST NAME column as shown below.
Filter by form
5. Click the Toggle Filter icon to apply filter. Access then extracts the record(s) that met the specified criteria. You may wish to save or remove the filter by clicking the Advance then clicking Clear All Filters.

FILTER BY SELECTION

This is another method of filtering a database. Here, Access extracts the records that met the specified criteria. Unlike the filter by form method, the criteria would be specified before filtering. This method gives you many filter options such as “Contains”, “Equals”, “Greater than or equal to”, “Less than or equal to”, “Between”, etc.

To filter a database by selection:

1. Open the database table in datasheet view.

2. Specify your criteria i.e. select any record’s information you wish to use as your criteria. For example, if I want to filter out all the medical students in my database, I will go under the COURSE column and select any field that contains the word “Medicine”.

3. Click the Home tab.

4. Click the drop down arrow beside the Selection command in the Sort & Filter group. Then select any option that suits your intention. In my case, I will select Contains “Medicine” as shown below.
Click selection
5. Access extracts the records of all medical students as shown below. You may wish to save the query result as a new query or you discard it.
Filter by selection output

FILTER BY EXCLUDING SELECTION

This is the third method of filtering a database in MS Access. It is just the opposite of filter by selection i.e. unlike the filter by selection that displays the records that met the specified criteria, it displays the records that did not match the specified criteria. This method gives you many filter options such as “Does not contain”, “Does not equal”, etc.

To filter a database by excluding selection:

1. Open the database table in datasheet view.

2. Specify your criteria i.e. select any record’s information you wish to use as your criteria. For example, if I want to filter out all the medical students in my database, I will go under the COURSE column and select any field that contains the word “Engineering”.

3. Click the Home tab.

4. Click the drop down arrow beside the Selection command in the Sort & Filter group. Then select any option that suits your intention. In my case, I will select Contains “Medicine”.

5. Access displays all the records that did not match the specified criteria.

ADVANCE FILTER/SORT

This is the fourth method of filtering a database in Access. It is the best method of filtering a database because it allows the user to filter a database using one or more criteria, unlike all the previously explained method that limits you to only one criteria.

To apply the advance filter in a database:

1. Open the database table in datasheet view.

2. Click the Home tab. Click the drop down arrow beside the Advanced command in the Sort & Filter group and select Filter by Form.

3. Select Advance Filter/Sort.

4. Access takes you to the query design view. Select the fields whose data you want to retrieve. You can select more than one field at a time by holding down the ctrl key and then clicking on the fields needed.

5. Click and drag the selected fields into the first field in the Field row (first row). Once you drop them in the first field, Access automatically fixes the fields for you. The Table row displays the table/query name from which the field came.

6. Set the query criteria inside any field of the Criteria row. 

For examples, to retrieve records of students whose age falls between 18 and above and whose names start with letter “J”, type: >="18" in the AGE field, then type: Like "J*" in the FIRST NAME field as shown below.
Advance query criteria
7. Click the Toggle Filter icon to apply filter. Access then extracts all the records that match both criteria as shown below.
Advance query result

NOTE:

You can set any number of criteria in advance filter/sort query method. I just set two for illustration. Now try to limit the age range to between 20 and 25.
How many records match the 3 criteria?

Recommended MS Access Textbook


ms access textbook kindle format
Click Here to know more about the book.


Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.

Recommended Entrepreneurship Books

the passionate entrepreneurs strategies book by buzzer joseph

The Passionate Entrepreneur’s Strategies: Learn how to discover business ideas that will be successful, grow multiple online income streams, make money from home, attract new and retain customers, applications of SWOT analysis in any business, skills that will help you succeed as an entrepreneur, self-discipline, etc. Lucrative Business Ideas Series Book 1. By Buzzer Joseph. Read more about the book.

how to make money from blogging and affiliate marketing book by buzzer joseph

How to Make Money from Blogging and Affiliate Marketing: Learn the Step by Step Guide of Creating, Growing and Optimizing your WordPress Blog from Scratch for Search Engines (SEO) and How to Make Money through Google AdSense, Affiliate Marketing and Other Strategies for Earning Passive Income Online. Lucrative Business Ideas Series Book 2. By Buzzer Joseph. Read more about the book.

how to make money self publishing kindle books by buzzer joseph

How to Make Money Self-Publishing Kindle Books for Beginners: Learn the Step by Step Guide to Self-Publish a Best Selling Book on Amazon KDP, How to Design Book Covers with Adobe Photoshop, Format, Edit and Proofread your Book Manuscript with Microsoft Word, Promote your Book on Various Online Platforms and Earn Monthly Passive Income Online. Lucrative Business Ideas Series Book 3. By Buzzer Joseph. Read more about the book.

diy grid and off-grid solar pv installation guide by buzzer joseph

Complete Solar PV System Installation Guide: DIY guide on how to install grid-connected and off-grid solar PV systems, how to build solar-powered electric cars, convert conventional cars to electric cars, online and offline ways to make money from solar energy sector (including stocks), electrical wiring and protection system installation guides, plus free resources for skill acquisition and personal development. Lucrative Business Ideas Series Book 4. By Buzzer Joseph. Read more about the book.

how to make money from cryptocurrency trading and investments by buzzer joseph

How to Make Money from Crypto Trading and Investing for Beginners: Step by step guide with screenshots on the basics of blockchain technology, bitcoin and altcoins, crypto security tips, cryptocurrency market analysis (FA and TA), long term and short term trading, how to calculate your profits in excel, legit ways to make money with cryptocurrencies, DeFi yield farming and staking, how to use any centralized and decentralized exchange platform and more, plus crypto investment bonus! Lucrative Business Ideas Series Book 5. By Buzzer Joseph. Read more about the book.

blockchain practical guide kindle book by buzzer joseph

Blockchain Practical Guide: Step by step guide with pictures, on how to monetize the latest blockchain technology trends: crypto trading and tools, especially Ethereum, DeFi, Staking, NFTs, Top 100 Play-to-Earn (P2E) and NFT games, Web 3.0, Metaverse, Virtual Reality (VR), Augmented Reality (AR), blockchain job opportunities, with crypto investment bonus! Lucrative Business Ideas Series Book 6. By Buzzer Joseph. Read more about the book.

how to make money with blockchain web3 apps kindle book by buzzer joseph and kelly joseph

How to Make Money with Blockchain Web3 Apps: Step by Step Guide with Screenshots and Videos on How to Make Money from Blockchain and Web3 Projects – NFTs, Play-to-Earn (P2E) Games, Move-to-Earn (M2E) Apps, Watch-to-Earn (W2E) Apps, SocialFi and GameFi Apps, with crypto investment bonus! Lucrative Business Ideas Series Book 7. By Buzzer Joseph and Kelly Joseph. Read more about the book.

No comments:

Post a Comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!