As more and more people line up to learn about Microsoft Access, it has become clear that the demand is present, and companies are finding solutions in this software that have always felt so intimidating by average users. In this commentary, you are going to learn the four basic parts of Microsoft Access and how familiarizing yourself with these four different objects will enhance productivity, keep your data safe and ultimately, provide peace of mind that any information you need to extract from the data set is at your fingertips any time it is requested.
First of all, there are four primary objects that are used in Access: tables, queries, forms, and reports. Queries, forms and reports do not exist without tables. A table is where all the raw information is either input manually or imported from another source, i.e.: Excel spreadsheet, another database, or an array of other options. Basically, there is no database without a table.
On the flip side, there can be many tables for one database. Database normalization/denormalization is the process of deciding how much is too much for one table. Storing all of a set of data into one table slows down the processing time when pulling queries or reports because Access will scan every row and column to search for the information requested. Whereas, processing time speeds up when the search goes quickly to one table to get one piece of information and another table to grab another piece of information. However, having too many tables also slows down the processing time, so the term normalization/denormalization makes sense. Of course, if you’re just not sure about developing multiple tables, using the Table Analyzer Wizard will help to recommend changes that need to be made in the structure of a database.
Once the table(s) are set up and in place, then the fun begins! Users can query the dataset to filter out information. Similarly, Excel uses Pivot Tables to create queries from raw datasets, even though it’s not called querying in Excel. A query that has been created in Access looks exactly like a table structure; the only difference is that it may not provide all of the information that is represented in the table. I call it a permanent filter. The query can then be saved as a separate object and retained in the database to view later. Subqueries are also possible in Microsoft Access.
In fact, Microsoft Access offers several different types of queries including parameter queries, crosstab queries, update queries and append queries. Parameter queries will prompt a user with a question before returning the results, i.e.: start/end dates or a value that you want to calculate a column by. Crosstab queries can display rows and columns of information such as taking a customer list and finding out how many people pay by credit card versus cash. An update query simply does what it says; it will update the query with new information that has been added to a table. And last of all, an append query will add new records to a query already created. New records may have been added to the table, as many databases have information that is added regularly.
A form is created mainly for ease of use and for security reasons. As you approach the more advanced stages of using Access, you will see how to separate the front end from the back end. The back end is the storage location for the raw tables that are not accessible by data entry clerks and other users in the organization. Yet, new data can be entered into the database table via forms. With the back end separated, no one can mistakenly make errors or edit data that has already been entered into the system. Likewise, no disgruntled employee has access to destroy valuable information.
Another benefit of forms is to set up conditional formatting for fields that are higher or lower than they should be or that require attention. For example, as a user scrolls through various records in form view, specific fields can be set to flag information such as low on inventory, high on turnover or usage, etc. This is done by changing a font color or shading a field on the form. Creating a form also provides options to autofill specific controls, which saves time for the person entering the information.
Lastly, reports are designed to generate information from the table in the preferred format. Formats include columnar, tabular or a variety of custom views. Spending a little bit of time to design a report upfront may save a lot of tension in the long run when a request is submitted for a report that has not yet been created or designed. Save time with those daily, weekly or monthly reports by having the template already set up, and then just update the report with new information added to the table.
Go one step further to group and sort a report by specific fields within the report. Are you responsible for creating reports that break down information by county or by region? Easy with Access. Just group them by the county or region field and sort alphabetically or numerically by a column set to a number data type. Customize a report by the page layout and learn how to select the page data so all of the rows adjust with the click of one button.
While the argument might be that users prefer Excel over Access, just know that Excel, even with the Pivot Tables to filter out information, cannot produce the professional and custom reports like Access can, and they do not offer a front end data entry method as the forms in Access.
Trust me, once users start to work with Access, generating tables, queries, forms and reports may become second nature. Its user interface is familiar to most people because of the standard Microsoft tabs and ribbon bars, so no requirements to learn another database management program is necessary. It is also cost effective because Access is included in the Office package. As of 2016, templates are also offered in Access, meaning that users who may be skeptical in building their own databases, may have a template to choose from that will do the designing for the user.
To sign up for the introduction class being offered again on October 21-22, 2019, visit LRS Education Services Microsoft Access, and click the “Register” button.Details of the class, along with an in-depth syllabus is also provided.
Looking forward to seeing you in class soon!
Kelly A. Cummings
Levi, Ray & Shoup, Inc.
Education Services, Instructor
217.793.3800
kelly.cummings@lrs.com