by Greg Creech, MCT, CompTIA CTT+

Many Administrative Professionals enter data into Access databases, generate reports, and work with queries. My Access session at TEC 17 goes beyond inputting and editing data and running reports; we explore Access’ and other relational databases’ objects.

Access and DBMS – Access is a Database Management System (DBMS) meaning that the database objects are contained in one file. Access and other DBMS applications have tables, queries, forms, and reports to track your important information. For enterprise systems, the tables, queries, forms, and reports may be separate vendors and applications and do not reside in one file.

Excel (flat file databases/tables) versus Access (relational databases/tables)

Flat file databases/tables, like many of those in Excel, or even Word, do not connect or link data to other sources and are stand alone. Relational databases link tables through primary keys with various relationship and join types. While an Access table may be stand alone, the power is linking the tables so changes are easier. Plus, with separate tables that are linked we can create historical data that Excel tables cannot do well nor efficiently. Another problem with flat file databases/tables is duplication. Access and relational databases handle historical data tracking more productively and are far better at preventing duplicates.

Four Primary Objects in Access and other DBMS

1. Tables – The primary purpose of a table is to contain and hold our data. Access tables and fields provide much data integrity and ease-of-use with lookup, yes/no, attachments, and a variety of other field types. Tables and fields may restrict certain data, for example date ranges, and limit users to choices in a lookup list.
2. Queries – Access and SQL queries bring tables together for reports and can have many types of criteria for displaying data. Using functions and calculations are popular in queries. While queries may be stand alone, almost all of Access pretty and powerful reports base themselves on queries. Queries easily export to Excel for further data manipulations and analysis.
3. Forms – We use forms to view, enter, and edit data in tables. Forms are prettier and more powerful that using tables for data entry and editing plus one form can update and enter data into more than one table. We use forms for menu systems, too. Forms can have calculations, functions, and other helpful tools in manipulating and managing your data.
4. Reports – Access Reports presents information in attractive and productive ways. Unlike forms, queries, and tables, reports do not interact with the data meaning reports are view only. Access is versatile with sharing and publishing reports for others using PDF, Word, and other formats. Reports can automatically provide totals, averages, and percentages. You may have very detailed reports or summary reports and almost all reports base themselves on queries.

Here’s a link for my favorite Windows 10 shortcuts. Enjoy!

I look forward to our time at TEC 17 where we will go into more detail about Access and these relational database objects. So, in March, put on your geek hat and let’s geek out to Access and databases in Tucson.

Greg Creech is a proud IAAP member since 2007 and speaks at IAAP events, including Summit and TEC conferences. He is an entertainer and educator combining music, humor, and technology into his presentations. He appreciates IAAP and the membership and travels around the country helping Administrative Professionals in technical and soft skills. His websites are gregcreech.com and gregcreech.biz and his YouTube channel is gocreech.He is a Microsoft Certified Applications Specialists—Instructor and CompTIA Certified Technical Trainer +.

For in-person training on this and many other topics, register for IAAP TEC17 today!