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