CAA292 ASSIGNMENT - 1: An Employee Database
We want to design a database for the employees working in our company. Start by planning the database:
What information will we need to keep?
Which part of this information is static? Which part is more changeable?
Think about storing this information in different tables. Would this be more practical? For example, you may think about storing the employees' personal information (name, address, date and place of birth etc) in one file, and his payroll information (gross salary, deductions, net salary, years worked, etc) in another table. In this case, which field will you use to link these two tables?
Start Access'97. Open a blank database and name it Employees.
1. Create a table to store the personal information of all employees.
- Save the table as personal info.
- Make the employee ID number , name and surname required fields.
- Set the employee ID number to be unique (no duplicates are allowed) and make the emploee ID number the primary key.
- Make the sex of the employee a list box with only two items: Male and Female.
- The Department will be selected from a combo box. (Enter various departments: sales, production, marketing, accounting, quality control. etc)
|Required field (This field cannot be left
In design view, click on the field. Under General properties at the bottom of the screen, find Required. Make it Yes.
Unique field (There cannot be more than one of the same data for this field)
In design view, click on the field. Under General properties at the bottom of the screen, find Indexed. Make it Yes(No duplicates).
Primary key (This is a unique field which we will use to link to other tables)
In design view, right click on the field. In the pop-up menu which appears, select Primary Key.
List box (This is similar to a combo box, but it remains open at all times. The user may select one of the given alternatives)
In design view, click on the field. Under Lookup properties at the bottom of the screen, choose List box. To offer the alternatives as data (male / female), click on Row Source Type and select Value list. Click on Row Source and write the alternatives you want to appear in the list box, separating each with a semi colon (e.g. male;female).
Combo box (This is a box which opens using the down arrow. The user may select one of the given alternatives)
In design view, click on the filed. Under Lookup properties at the bottom of the screen, choose Combo box. To offer the alternatives as data (sales, production, marketing, etc.), click on Row Source Type and select Value list. Click on Row Source and write the alternatives you want to appear in the combo box, separating each with a semi colon (e.g. sales;marketing;....).
2. Using the form wizard, create a form for data entry to the employees.
- Use the justified layout.
- Choose personal info as the data table you want to use.
- Use whichever style background you wish.
- Make the title of the form Employee Personal Information.
- Using the form design view, drag and drop the fields to suitable positions on the form.
- Change the caption of field labels and select the special effect which you like the most.
- Insert the company logo at the top right side of the form. (Use the Bilkent logo for example).
|Caption (If a caption is not defined for
a field, field names defined when creating the table
appear directly on the form. )
In design view, click on the field name. Edit the label as you wish. Or, right click on the field name. Select properties, and change the caption.
Special effect (You can change the appearance of captions)
In design view, right click on the field label. Select special effects from the pop up menu that appears.
Inserting an image
You can insert a permanent image to appear on every page either by copying the image from another document an pasting it onto your form in design view, or selecting insert picture from the menubar, and selecting the file you want to insert. You can change the appearance using special effects, resizing the image, dragging and dropping it to the position you want, etc.
3. Using the form in normal view, enter the data for a few employees (at least 5-6). Save and close the form. Open the personal info table. Does the data which you entered using the form, appear in the table correctly?
4. In the same database, create a new table for the payroll of employees.
- Name the table payroll.
- Make the employee ID number required, unique and set it as primary key.
- Make the date employed short date type and make an input mask in the form ##-##-##
- Choose currency type for the gross salary. Don't define other fields for deductions or the net salary, we will be using calculated fields in our form.
5. Using the form wizard, create a form for payment of employees.
- Name the form EMPLOYEE PAYROLL
- Use the table payroll in your form. Arrange the fields on the screen according to your choice.
- Now we come to calculated fields: The user will only enter the gross salary of the employee. Your form must calculate the income tax as 35% of the gross salary, and the insurance premium as 10% of the salary after income tax deduction. The net salary is what is left after income tax and insurance premiums have been deducted. I rely on CAA students for more realistic formulas on how to reach the net salary from the gross, or, gross from net :)
|Calculated Field (This field will use values from
the table, or value entered on the form to calculate a
certain result. This result will appear on the screen,
but will not be written to the database).
In design view, press on the toolbox icon on the menubar, so that the tool box opens at the side of the screen.
- From the toolbox, choose rectangle (bottom left of the toolbox) and draw a rectangle on the form where you would like your result to be.
- From the toolbox, choose text box (upper right of the toolbox) and draw a text box inside your rectangle about the same size. When you release the mouse, it says unbound within the text box. Unbound means that the result calculated here, will not be written to the database.
- Right click on the textbox and select Properties. Click on the Control Source option. You will see a button with ... on it. Click that button. The Expression Builder helps you build arithmetic expressions. Like Excel, calculations must start with the = sign.
- From the list below, you can click on the field you want to use in your expression. Notice that fields used in calculations appear in [ ] signs. Later on, you can write your expressions without the expression builder if you wish.
- Click OK and exit the Expression Builder. Close the Properties display.
6. Use the form in normal view, and enter some data. Watch the form automatically calculate the deductions and the resultant net salary after deductions.
7. We want to create a report showing the id, name, surname, department and payroll details for each employee. Notice that you will need to take the first part of this information from the personal info table, and the others from the payroll table. Therefore, first you need to form a relationship between these two tables. Here, we need to show that the two tables are linked through the employee ID number.
|Creating a Relationship (Showing how to link two tables
so that information in both can be reached)
- With only the database window open (don't start the Report Wizard yet), click on the Relationships button on the menubar.
- From the Show Table window, click on the tables you want to incude in the relationship and click Add. These tables appear in the Relationship window.
- From one of the tables, click on the field you want to link to the other table, and drag it onto the same field in the other table. When you release the mouse button, you will see a line linking the two fields appear in the relationships window. Now you have linked information in both tables through these fields.
- Save the relationship.
8. Now, using the Report Wizard, create a report showing the id, name, surname, department and payroll details for each employee. Notice that you are allowed to select fields both from the personal info table, and the payroll table, because you have already warned the database that these two tables are linked.
- Don't put any grouping levels
- Order the employees in alphabetical order according to name.
- Choose a tabular output.
- Choose any style you wish.
- Notice that you can only get the gross salary from the payroll table. You must add calculated fields for the income tax and insurance, and the net salary. Use the same method as you did when creating the form.
- Save the report.
9. Close the design view and Preview the report. Access will ask you for the ID of the employee to preview. Give the ID of one of your employees. View the report and make any changes you wish.
Homework: Design a database for members of a sports club. Prepare a table for the member's personal info. Let's say we have two types of memberships: Single and Family. Make TYPE a combo box from which the user will select his membership.
Further Research: Find out the items kept in a real-life payroll database. Which of these are calculated fields, which require data entry by the user? Modify your database according to these findings.