Microsoft (MS Access)
It is one of the easiest and the most popular database software for creating storing and managing Database Management System. MS Access provides database properties and startup options to meet most of the customization needs. The startup options facilitate protection of the standard Access settings in the database that may be in conflict with their application needs, from being modified by unauthorized users. It also ensures that, when the applications are distributed, other programs that are in use do not override customer settings and also tests their application under different configurations.
A data type is stored different types of values in a column in a table. For example, the data type of the column Customer Name that contains names of all the customers will be text and the data type of the column phone no. that contains the phone numbers of the customers will be number in the customers table.
Methods of Creating a Database
Access offers the following two methods of creating a database:
Using the Database Wizard - This method enables use of a Wizard that provides sample database objects and guides through the steps of creating a database and its objects like tables, forms and reports.
When we create a table using Database Wizard, the Database Wizard provides a framework of the table, ie., number of columns and rows. These specifications can be later modified for suitability.
Using a Blank Database - This provides a blank database and enables you to create all the database objects from scratch. When we create tables using blank database, tables have to be designed within the database.
Methods of Creating a Table
It must be borne in mind that data tables are always created within the database. It means that, for creating tables, the database must be open. The database screen, when opened, appears as given below. We will check on the tab 'Create table in Design view'.
Methods of Entering Data in a Table
- Using the Datasheet View - This method enables you to enter data in a row - and - column format
- Using Forms - This method enables you to create a form to enter data in a table. A form is a user - friendly method of entering data in a table.
Referential integrity is implemented to ensure that the relationship between the primary key and the foreign key table is maintained. When referential integrity has been enforced between two tables, you can:
- Cascade Update Related Fields - This enables a change in the value of the primary key field to be automatically updated in all the tables where the field has been declared a foreign key.
- Cascade Delete Related Records - This enables the automatic deletion of all related records when the primary key record is deleted.
Arrangement of Data in the table
Sorting Data - It helps in arranging the data in a systematic order for grouping and quick search. You can sort a table by columns, single or multiple, in an ascending or descending order.
Filtering Data - It enables us to display only those records in a table that meet a specified filter criterion. Assume that you have a table that contains data about all the customers and the regions they belong to. You now require information only about the customers in the northern region. A filter can enable you to specify the filter criterion as North and only those records of data matching the criterion will be displayed.
Access enables you to create the following types of Queries -
- Select - This type of query displays data from one or more tables, based on user - defined criteria.
- Find Duplicate - This type of query displays all records with duplicate values in a specific field. For example, you can find out details of customers who have bought more than one product.
- Find Unmatched - This type of query displays all records that do not have a matching record in another table. For example, you can find details of customers who have not bought any product.
- Parameter - This type of query, when executed, displays a dialog box in which you can enter the criterion for retrieving data. This enables you to customize the output of your query based on the value that you enter while executing it.
- Crosstab - This type of query calculates a sum or count and groups the results of the query. For example, you can display the items grouped first by Category and within the category by suppliers.
- Update - This type of query is used to make changes in the records in a table. For example, to change the price of a product from Rs.10 to Rs. 12.50.
- Append - This type of query adds records extracted from one table to another table.
- Delete - This type of query deletes records from tables, based on a specified criterion.
- Make - Table - This type of query creates a new table from a group of records extracted from other tables.
Access provides the following two methods of creating a query:
- Using Design View - The Design view enables you to create a query from scratch. It enables you to specify the tables, fields and criteria of a query.
- Using Query Wizard - The Query Wizard displays a series of dialog boxes in which you can specify the tables and fields that you want to include in the query.
Forms and Reports
Access forms provide a user - friendly interface to enter data in a table. These enable us to-
- Enter data in a table
- Modify data in a table
- Delete data from a table
- Browse the contents of a table
- Print data in a table
- Using the Wizard - The form Wizard of Access automatically creates a form based on the fields, layout and styles that you select.
- Using the Design View - The design view enables you to create a form from scratch. It is easier to create a form using the Form Wizard and later use the Design view to make the required modifications to the form.
Creating Billing Format in MS AccessLike the creation of Voucher Form, we can also create different types of Invoice Forms to suit our requirements. To create the form, first, we have to create a table containing all controls like Customer ID, Customer Name, Bill Date, Address of the Customer, Contact Person, Type of Service, Narration, Fee Amount, Service Tax, Grand Total, etc., based on which the Form is created. In this context, we have to express the property of each content of the table. For example, customer ID should be either auto generated or in number form, Customer Name, Address, Contact Person, Type of Service and Narration should be in text format and their limit of fields (characters) should also be defined. Accordingly, the date should be defined in the date format, and Fee amount, Service Tax amount and Grand Total should be defined as currency Format.
Adding Controls to Forms
Controls: These are items in a form that are used to display data to perform specific actions like adding a record or deleting a record from the table. Controls are used to create a link between the form and a table. For example, the name of the client is displayed in a Textbox control where the Textbox control is linked to the Client Name field of the Clients table.
Controls can be categorized as:
- Bound Controls - A bound control is linked to a field in a table. These controls are used to display the value of a field, accept a value in the field, or modify the value of a field in a table.
- Unbound Controls - An unbound control is not linked to any field in a table. These controls are used to enhance the appearance of a form or display information that is not linked to a field in a table.
- Calculated Controls - A calculated control is used to display a calculated value based on one ore more fields in a table.
Creating a Report
Software or Program using Database Management System but without the facility of generating reports is incomplete. Reporting is one of the main purposes for which an accounting system is designed. The output of accounting system takes the form of accounting reports. So we should always consider the software which offers flexibility in generating customized reports. There are two types of generating reports - Programmed and Casual Reports.
Designing Accounting Reports using Access
Generating Financial Accounting Reports like Cash book, Bank Book, Ledger Accounts and Trial Balance based on Access database tables is the main feature of MS Access. It is very easy to create Accounting Reports mentioned above through Access by using relevant SQL statements. Let us now consider the exact process in the context of generating Trial Balance.