Continuing our series of Tips – Tricks & Guidelines with Microsoft Access we share some additional items for the month. The ongoing articles will continue to focus on the tables within an Access database.
Filtering within a table
When querying for information in an Access database, the first thing that comes to mind are Queries and Reports. However, if you need some basic information or want to get a quick glance to some specific data, the user can gather the information with the table view through filters on the table.
For example, what if you simply want to know orders from 2 specific states. While the table is open, simply right click in the field you want to filter > right click > select Text Filters and then enter the states or items you want to filter for. See the below figure.
Once an item is selected, the results then appear in the table.
Tip – Sometimes, the filtered data is something that you may do on an ongoing basis. Another option on the tool bar is the Advanced Filter/Sort. See the below diagram.
When selecting this option, the user is sent into what looks like the design of an Access query. At this point, there are many options, but for the purpose of this article, you now have the option of what started as a quick filter to save the filter as a query. Simply right click on the query grid and select “Save as Query” See the below diagram.
Now you have saved a basic filter which can be used again as a query in the database for future reference.
Another option for filtering while in a table is the option to “filter by form”. Again, this is used mainly as a very basic method to search for data within a table quickly and easily. See the below diagram.
Once the filter by form is initiated, then all the records in the table disappear and then each of the fields will show drop down lists on all the fields. Once the dropdown is selected, then it will show the unique values for each column or field. See the below diagram.
Select the item you want to filter for and then those records will be displayed. If more advanced methods or querying are needed, then a select query would most likely be utilized. Additionally, if the table has more than 100K records, then for better performance a standard Access query would be a better choice.
These are just a few time saving tips when using Access tables. If you are having trouble knowing how to get started with Microsoft Access, reach out to Arkware today for any database needs.