ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Overview of Microsoft Access

Updated on May 26, 2011

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).

Click thumbnail to view full-size
Customer Data (click to enlarge)Order Data (click to enlarge)
Customer Data (click to enlarge)
Customer Data (click to enlarge)
Order Data (click to enlarge)
Order Data (click to enlarge)

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.

working

This website uses cookies

As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://corp.maven.io/privacy-policy

Show Details
Necessary
HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
LoginThis is necessary to sign in to the HubPages Service.
Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
AkismetThis is used to detect comment spam. (Privacy Policy)
HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
Features
Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
MavenThis supports the Maven widget and search functionality. (Privacy Policy)
Marketing
Google AdSenseThis is an ad network. (Privacy Policy)
Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
Index ExchangeThis is an ad network. (Privacy Policy)
SovrnThis is an ad network. (Privacy Policy)
Facebook AdsThis is an ad network. (Privacy Policy)
Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
AppNexusThis is an ad network. (Privacy Policy)
OpenxThis is an ad network. (Privacy Policy)
Rubicon ProjectThis is an ad network. (Privacy Policy)
TripleLiftThis is an ad network. (Privacy Policy)
Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
Statistics
Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
ClickscoThis is a data management platform studying reader behavior (Privacy Policy)