Overview of Microsoft Access
January 30, 2008
Before you go out and plunk down some serious dollars (or euros or pounds) on lengthy and involved Microsoft Access courses, it might be a good idea to know what it is and how it's used. That is the focus of this hub.
Microsoft Access is a "Relational Database Management System." The description that follows applies to Microsoft Access 2000, Microsoft Access 2002, Microsoft Access 2007 Microsoft Access 2007 & 2010, and even Microsoft Access 97. In fact what follows applies to just about every Windows database out there regardless of who makes it.
What is Does
Access can store data in specific formats for sorting, querying, and reporting. Sorting is pretty straightforward; data is simply presented to you in particular orders. An example might be presenting your customer data (customer number, name, address, city, state, zip, and total purchases) in last name order.
Querying means that as a user of this database, you can ask Access for a collection of information relating to location such as state or country, price as it might relate to how much a customer spent, and date as it might relate to when items were purchased. Querying can include sorting as well. For example if you wanted to see the top spending customers in the state of Florida querying would be a way to do that. A Query on data typically returns a sub-set of the collection of data, but can return all of it in a different order as well.
Reporting is simply query results in printable or viewable form.
How the Data Are Stored
In order for Access to perform these functions data has to be stored in the smallest possible units. These units are called fields. A field might contain a first name, a last name, a middle name, a street address, and so on. Notice that I do not propose that the entire name be placed in one field. If that were done the only sorting one could perform would end up being presented by the first name. Hardly useful. But if a separate field is used for the last name, another for first, and so on, much more useful sorting can be accomplished.
Fields are also defined as a type of data (number, text, date, date-time, dollar, etc.). By storing data in its own specific field type, Access (or any RDBMS for that matter) can sort that data in very tightly controlled ways. For example one can sort numbers and alphabetic content accurately as long as Access knows what type of sort to apply to that data. Thus the field type.
An entire collection of fields relating to a particular entry is called a record. The entire collection of records is called a table.
Tables resemble spreadsheets in that they are a grid of data. A row represents one complete record and a column a particular data field. Thus a data table containing a collection of customer demographics might contain the Customer Number, Name, Address, City, State, Zip, Telephone Number, Cell Phone Number, and email Address.
Possibly the easiest way to visualize this is to imagine a data table as a spreadsheet. Each column would be a field, each row a record, and a collection of rows would represent the entire data table. Naturally each row, in the case of of a customer file for example, would be one customer.
By storing data in this manner it is much easier to sort and report on that data in nearly any order you wish.
All of the above could describe a Database Management System DBMS, but Microsoft Access is also a relational database management system.
Relational Databases
A relational database management system (RDBMS) allows for the creation of multiple tables that can be linked together via one or more shared field values between tables.
For example the Customer Table mentioned above could be linked to a table containing more sensitive purchase or credit card information. As long as both tables contain a Customer Number field with the same data size and type these tables can be linked or related. Of course each Customer Number would be unique to the customer.
In this example the secondary or child table could contain the following information; Customer Number (identical in format to Customer Number in the parent table), Product ID, Unit Price, Quantity, etc. Yet another child table could contain the Customer Number, credit card data, including the number, active til dates, and pin number.
As long as there is a common Customer Number (in this example) the tables can be linked or kept separate depending on the level of security required of this information. This way two of the tables could be displayed on a form, through a query, or on a report and look as though all the information is stored in one place.
Access, like any RDBMS, will allow these tables to be interrelated via forms, reports, or queries. Access, as with many other RDBMS, can use Structured Query Language (SQL) to query the table(s).
Though Microsoft Access is not as powerful (and is properly a psudo-rdbms) as other products such as Microsoft SQL Server, Oracle, MySQL, Sybase, or IBM DB2, it operates in much the same way and many of the SQL statements that would work properly in Access could be also used in the above mentioned RDBMS without modification.
Finally, databases are used almost ubiquitously. One. though I don't know which, is certainly used by HubPages to keep track of hubbers, their articles, and the number of impressions each article rates.
Why Access?
Beside the very easy to use interface for the creation of forms, queries, and reports Microsoft Access also has a built in programming language called Visual Basic for Applications (VBA). VBA can be very useful for importing data (daily or repeatedly) from other applications, for doing complex calculations, and other more rigorous tasks.
Because Access is a Microsoft product it is quite easy to import (copy information into a table) Excel spreadsheet data into Access. Access can also import data from a wide range of other programs and applications.
In short Access is quite powerful, even though it is not nearly as robust as MS SQL Server, Oracle or Sybase.
Terms
Relational Database Management System: Data that are stored and manipulated are held in a relational manner. e.g. The tables within can be related to each other via fields.
Field: A single data item within a data record. The field is usually represented as a column.
Key Field: A field that contains like data that is used between tables to link or relate them. Key Field usually refers to the linking field in the parent table. In Access, this must always be the first field in the parent table.
Foreign Key: Like the key field above a field that contains data that can be used to link or relate two tables together. The Foreign Key usually refers to the field in the child table; it does not have to be the first record in the child table.
Record: The row of information representing one set of data within a table.
Parent Table: The primary table used to coordinate and connect to child tables. It might also be called the master table.
Child Table: Another table which can be related to the parent table. Think of this as a "slave" table; a table that is designed to be used with the Master table. Note: that Child Tables can also be master tables to lesser child tables.
Table: A collection of like data arranged in a spreadsheet (rows and columns) like fashion.