Home

So, what is MS Access?

MS Access is a data repository system whereby data can be stored, extracted, organised and presented in order to aid with reporting and analysis.

It is often referred to as a departmental solution for data issues but because it is so sophisticated can be used for complex data warehousing tasks.

Previous versions of Access (97, 2000, 2003, and 2007) were client only applications but due to the ability of SharePoint to house an Access project, Access can now be used as a fully functioning web application.

Access features several components:

Tables

 

Tables are 2-Dimensional files that store data. They consist of columns of data (known as fields) and rows of data (known as records). They use data validation to ensure that the correct type of data is stored in the correct field.

 

 

When a table shares a field with another table they are said to be related. An example of this would be a table for teachers and classes. A class will contain a teacher so a table for classes will contain a field for teachers.

Queries

 

Select queries can return all records in a table but are normally used to return filtered data for forms and reports.

 

 

Calculated fields can also be used which means that Access doesn’t have to store all data in a table.

Queries are created using the Query designer:

Fields from tables are dropped into the field box in the query designer and data can be filtered by creating expressions in the criteria box.

After the relevant information has been added the query can be run and will produce the relevant subset of records in datasheet view:

 Forms

 

Forms are used in Access to both display records and to enable users to edit data stored in a table. There are many different ways forms can be created although generally they either present all the records from a subset of a table or tables (continuous forms) or they present a single record for editing (data entry forms.)

 

Continuous forms allow users to view all records in a table or query flowing vertically down the page. They also have buttons which can be used to open related forms or reports.

Data entry forms present one record in detail and allow it to be edited. Use of validation rules can prevent the wrong data from being entered into the wrong fields and use of tabs and navigation buttons can make it easier to locate and present data.

Reports

 

Reports are one of the most powerful features in MS Access. They are used to summarise data and present it in a way which is relevant, easy to understand and useful for the end user.

 

 

In MS Access reports use the banded design concept whereby (similar to continuous forms) records from a table or query are displayed flowing vertically down the page.

A standard type of report would be a summary report where records are presented in summary form, often having been grouped, sorted and totalled.

Reports can also contain sub-reports which are linked to display related data. These types of reports are often referred to as “Detailed” reports as they provide detailed information about a particular subject.

Macros

 

Macros are used in MS Access to automate tasks. They are an excellent alternative to using Visual Basic for Applications and are simple and easy to use.

 

 

Macros can be used for tasks such as saving a form or report, closing a window, sending a message to the user, opening a query, etc.

The macro user interface has undergone many changes since it was first introduced but has settled on a code style interface with indents in Access 2010.

 

 

3 Comments

  • dadaboss92 says:

    Hello
    I followed your tuto for a combo box that depends on another one. Good tuto because it seems that lot of people suggest to use vba.
    I have got a question about your tuto. When you have got 3 combo box linked, after you filled them if you change the 1st, the 3rd doesn’t become blank.
    Any suggestion ?
    Thank you

  • Greg Shulman says:

    Hello!
    In your invoice tutorial you mentioned that the “Invoice” topic could be a whole separate series of tutorials. Did you ever produce one? I am looking for some help designing an invoice capable of handling 2 differently applied taxes, with or without shipping, layaway sales, and a couple of other little quirks particular to our business.
    Thank you,
    Greg

    • admin says:

      Not yet. I will be releasing some tutorials on Access 2007 in the near future where I create an invoice and do it in a bit more detail which would (to a certain extent) answer your question.

      Regards