Expense Reports database template
The past few days I have been working on an update to our Access 2000 Expense Report database. I'm interested if anyone has any feedback on how I can improve the design of the template.
Let me give you a quick run through of the tables, forms, and reports. The schema includes four tables (Employees, Expense Reports, and Expense Details, and My Company Information). The [My Company Information] table isn’t really used except to store the [Mileage Reimbursement Amount] value. Here is what the relationships looks like:
The Expense Report’s Status column is a value list of New, Pending, Deferred, and Completed. This setting drives much of the report and workflow aspects of the database. The user first boots to the Expense Reports List which only shows New and Pending reports.
Drilling through to the Expense Details form is the heart of the application.
Few interesting things about the form:
1. The fields that are required include Report Date, Employee, Approver, and Business Purpose.
2. Category list is pre-populated with expense categories. I thought about making this a lookup table but elected to keep it simple.
3. When changing the Category to Mileage Reimbursement it prompts via a macro for the number of miles. A formula then multiplies it with the [My Company Information].[ Mileage Reimbursement Amount] field and updates the cost field. This only works for trusted databases. If the database is not trusted a message box will tell the user about the formula.
4. The email icon next to the Approver field will send the expense report in a PDF to the Approver copying the Employee.
5. Clicking on the Approve All Details icon will run and update query that marks all of the expense reports line items as approved. The totals will update date automatically.
There are three reports in the database:
· Closed Expense Reports – Shows the expense reports where Status = Paid or Deferred.
· Expense Report – This report shows the Expense Report with the line items.
· Expenses Grouped by Category – Shows the line items for all expense reports where Status = Paid.
Here is the latest draft of the Expense Report:
As with all of the contacts in our templates I have included an Employee List and Employee Details forms. The Employee Details form allows you to see submitted expense reports for the selected person.