Microsoft Access is a Database Management System (DBMS) that provides tools to create relationships between information and provides a user interface that is easy to understand, but flexible enough that it can house the most complex of processes.
Microsoft Access provides its own data storage services as well, which in my opinion, puts it in the forefront of programs one should learn when trying to grow their technical skillset. Other DBMS’ for example, like Microsoft SQL Server, or Oracle may store data, but they then require some form of programming knowledge in order to retrieve it.
Before we dive further into what MS Access is, what it looks like, and how to work in it, I would first like to go over a few terms that we will be using later on.
A table is a place that you store information in a database; think of it like a Microsoft Excel Spreadsheet. You have your columns, columns headers, your rows, and your data.
A query allows you to pull and review a table(s) information. It can be used to Select only columns you want to see, to filter out information, to add information together, or to relate two tables of data together to combine them.
A field is a column in a table or query. Whereas in Excel you have columns A, B, and C, in Access, the field is typically represented by a field name. In an Employee Table for example, you may have Fields called “EmployeeID”, “Name”, “Country”, “Zipcode”, and “Address”.
Note that fields can also be referred to as a number, starting with the number 0. In the above example, Field 0 would be EmployeeID and Field 3 would be “Zipcode”
A record is a row of data in your table. Similar to Excel’s rows 1, 2, 3…
Most tables you make or use will have have a Unique Identifier (UID). A Unique ID is something that distinguishes a record from all other records. An example would be “EmployeeID” or “Social Security Number”. No other record in that table should have that number.
EffectiveDate or AsOf_Dates:
As a table is used throughout time, you may want to make use of an Effective Date field. And effective date allows you to make multiple records with the same Unique ID, but have them dated to a period of time. For example:
On 10/1/2005 John Doe lived as 1234 Park Place.
On 5/1/2012 John Doe moved to 1111 New Address Road.
Having a history of data is useful if it is ever needed; however, if I wanted to select the most recent/current Address, I would want to query on the most recent EffectiveDate for John Doe.
Criteria and Where Clauses:
Criteria, or “where” clauses, are placed in a query to filter for the data that you want to see. The employee table for example could have ten thousand records in it for ten thousand Employees, but if I only want to find the employee information for John Doe, I would place his Name, or better yet, his EmployeeID in the query criteria to only pull his record(s).
In a database, a relationship is something you make between two tables. Think of our Excel VLOOKUP for this one. We have two tables (two data sets). One table is Employee Names, the other is Employee Addresses. Both Tables have EmployeeID, our unique identifier. We make a relationship, or “Join”, the two tables by EmployeeID in order to tie the data together.