ACCESS PART 2

Review of some Access concepts

Database Tables:

Table:

Key field – Unique Identifier ; Will identify the row

Query and Reports:

Import from Excel:

Link to Excel:

Table Exercise:

Using the Timesheet Exercise: (which was found here: Access Handout.htm)

1) Add a new table called department. It contains the 3 character department and the 25 character department name.

2) Add the new field department to the employee table.

3) Change the timesheet query to have list the department and department name for each employee.

Form Design

1) Wizard review

2) Choosing file

3) Adding fields

4) Moving fields

5) Field properties

6) Combo box

7) Adding buttons

8) Ctrl f to find

Exercise for creating and changing forms:

1) Use the employee and timesheet file database (See first Access exercise)

2) Create an input form for the employees if that was not done. Just use the wizard.

3) Create an input form for the timesheet using the form wizard if it was not already done.

a) Change the employee to a drop-down box if it was not already done.

b) Change the date format (using properties) to a “short date”

c) Move the fields on the form to another location.

d) Change the heading of “Date” to “Worked Date”

e) Add the title “Timesheet Entry” to the header with a font size of 20.

f) Form mode - Use the form to find a specific Employee that already has a timesheet record. (Use CTRL-F)

g) Back to design mode - Add a command button to find a record.

h) Form mode – Use the new button to find that same timesheet record.

4) Create a new form called “Menu” using the design view.

a) Insert a command button to open the Employee Entry form.

b) Insert a command button to open the Timesheet Entry form.

c) Form mode – Use the menu

Report design

Wizard review
Choosing file
Adding fields
Moving fields
Field properties
Summation
Report sections
Visibility of sections

Report Exercise:

1) Create a report on the employee timesheets if that was not done. Just use the wizard.

2) If you had created a report, please delete it.

3) Create a new report on the timesheet query using the report wizard, but be sure to include the summary options. Create a report that shows summary and detail. Sort by the Employee number. Do not include the department number. Call it DETAIL REPORT.

a) Change the DETAIL REPORT to do the following:

b) Add the Department number and remove the employee first name.

c) Summarize the total hours worked and amount paid for each department and each employee.

d) Sort by Department number first and the employee number second. Summarize for every department.

e) Change the heading to a font size of 20 and change it to say “EMPLOYEE TIMESHEET REPORT”