ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Access Database Basics

Updated on December 6, 2010

What is a database?

In its simplest form, a database is a tool for collecting and organizing information. Databases can store information and are used all over the world for a variety of tasks and duties. There are many different types of Databases and they each have their own positives and negatives - however one of the most common in use today is Microsoft's Access Database.

As this product integrates closely with the other products in the Microsoft Office range it has some inherent ease of startup in that some of the menu commands and ribbons are in comfortable and familiar places.

Databases started as a way of better organizing the data that people previously tracked by use of spreadsheets and lists. With an abiilty to have a much greater analysis of the data available it simplified the massive spreadsheets that people were accustomed to building for there day to day work.

You can think of a database as a simple container of objects. As such a database can hold data in a variety of different formats and is not limited to simple text. An Access database is known as a "flat file" database - this basically means that it stores all of the information in a specific database in one single file. So all the tables, forms, reports etc... are all actually combined in on single .accdb file (note in earlier versions of Access this file extension would have been .mdb).

Tables in an Access database

At first glance you would be very confused with tables in Access as they are the same as Excel worksheets.  However this is their strength as the familiarity you already have with Excel will enable you to utilize tables very easily and to also import the data or spreadsheet directly into the database table.

The main difference between your spreadsheet and the database table is how the information is organized.  Data in Access is organized in a unique manner and information about a specific item should only be entered once to ensure that you do not have redundancy.  For example data about products would be entered into one table and the corresponding salesperson information would be entered into another table.  This process is called normalization.

Table rows are referred to as records, with each record being an individual piece of information.  However each record can have many associated fields (the table columns) so that you have the "full picture" for each item that you are trying to record.  So assuming that you have setup your salesperson table, each record (row) would contain information about a different salesperson with each field (column) having some more detail about them - such as first name, last name, phone number, email address, mailing address etc... 

Each field can have a different data type allowing you even more personalization and customization so that you can specify text, date, number or any other type.

Forms in an Access Database

Forms simplify the entry of data.  Once you've built your initial database and structure, you don't want to have to revisit those same tables again and you definitely do not want your users accessing the tables directly.  The way to ensure that they are able to edit and add information to your database is through the use of Forms.

Forms often contain command buttons and other controls that perform various tasks and you can use these controls to open other forms or reports and by utilizing your forms in a logical fashion you "tie together" many different tables enhancing the capabilities of your database.

Reports in an Access Database

Reports are basically the output of your database.  While its fun to enter data, you actually want to utilize that information in a certain manner and by using reports you are able to format, summarize and present data in a clear and logical fashion.

Generally each report is built to answer a specific question - for example how many sales did person "y" make?  But you can also make it a lot more generic - how many sales did team "x" make?

A database report is a "real time" representation of the information that is in the database.  Depending on the size of your database you can run a report at any time - I would suggest you wait till off hours to run significant reports as if your database is very large, this could impact the speed and responsiveness of your application and impact your users.

Queries in an Access Database

Queries are used to "pull" your data.  Just like a form is an interface used to put new data into your tables, queries are used to pull data from your tables so that it is presented to you in a meaningful fashion.  You can think of the Query as being similar to the formula's that you would have used in Excel but because you're able to pull data spread across several different tables, the functionality is significantly greater.

Queries let you add criteria to "filter" the data down to just the records you want and in addition to this, Queries also give you the ability to update data in the underlying table also!  They are an extremely powerful tool and come in two different flavors:

  • A select query - pull and forget.  A select query retrieves the information that you are interested in and makes it available for your use.  The results from a select query can be viewed on your screen, printed out or copied.  In addition you can use the results from a select query as the input into another query which really builds on the capabilities of your database.
  • A action query - pretty self explanatory really.  An action query does something with the output of the data.  This could be adding or deleting data from an existing table or even creating a new table!

Macros in Access

Macros are a way of enhancing the functionality in your database.  For example a simple macro would be to run a report when you press a button in your form. 

However macro's can be much more complex and with the abiilty to "record" macro's in both Excel and Access you can simplify your overall data entry experience greatly!   Most database operations that you do manually can be automated by using macros, so they can be great time-saving devices.

Comments

    0 of 8192 characters used
    Post Comment

    • My SciFi Life profile imageAUTHOR

      My SciFi Life 

      7 years ago from London, UK

      Hi Nicomp - thanks for reading. Yes I've taken some courses on Access and other tools and the ease of use and functionality in Access definitely makes it stand out for me.

    • My SciFi Life profile imageAUTHOR

      My SciFi Life 

      7 years ago from London, UK

      Hi Nicomp - thanks for reading. Yes I've taken some courses on Access and other tools and the ease of use and functionality in Access definitely makes it stand out for me.

    • nicomp profile image

      nicomp really 

      7 years ago from Ohio, USA

      Nice overview and intro to Access. The form tool and the report tool are still market leaders even after all these years.

    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://hubpages.com/privacy-policy#gdpr

    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)