Algorithm for relating tables and generating Reports in MS-Access

1. Start
2. Click on Start Button à All Programs à MS Office à MS-Access
3. A new blank database is opened. Use the Database wizard, where Tables, Queries, Forms, Reports, Pages etc., will appear on the left side in the wizard
4. Click on Tables and create a table in Design view or by using wizard or by using entering data option to create the table structure with the given fields as columns with appropriate field names, data types to each field and click on Edit à Primary key option from menu bar to one appropriate column for two tables.
5. Save it by entering table names to both the tables
6. Enter the data properly to Employee Code, Employee Name and Basic Pay appropriately for table1
7. Enter the data properly to Employee Code, Basic Pay appropriately for table2
8. Click on Tools à Relationships option to relate the table1 and table2 by dragging Employee Code, which is the Primary key here
9. Click on Queries and create a Query in Design view or by using wizard to create a query with Employee Code, Employee Name, Basic Pay by selecting the appropriate fields from each table
10. Now add D.A. as an expression using function as : IIf ( [BasicPay]>=20000, [BasicPay]*40/100, IIf ( [BasicPay]>=10000 And [BasicPay] < 20000, [BasicPay]*30/100, [BasicPay]*20/100 ) ), H.R.A. as : [BasicPay]*25/100, and Gross Salary as : ([BasicPay]+[DA]+[HRA]) by entering the above formulae
11. Save it by entering a query name and double click to see the rows of the query
12. Report1 is created by using wizard view by following step by step process by including the required columns Employee Code, Employee Name, Basic Pay, D.A., H.R.A., and Gross Salary created while Query creation and finally save the report with an appropriate name
13. Report2 is created by using wizard view by following step by step process by including the required columns Employee Code, Employee Name, Age, Gender and Gross Salary created and finally save the report with an appropriate name
14. Double click the Reports to see the results of the reports in the desired format
15. Close the database
16. Stop