Access Updates With Web Form 24.07.08
A database to track maintenance activities for a portfolio of commercial properties. The tables and reports contained in the MS Access Service Call Management template are very similar to what I have in mind.
Techs will enter their daily activities and hours in the data base via the web from their homes in the evening. The company’s bookkeeper will use the information to both compute payroll and on a monthly basis to bill the company’s clients. Access to certain areas of the database will be controlled.
Typically, maintenance tasks fall into several categories such as electrical repair, plumbing, roof repair, landscaping, etc. The input form for the maintenance tech should include the following fields:
1) Name:
2) Property:
3) Date:
4) Hours performed:
5) Type of task:
6) Narrative description of services performed:
While the tasks are similar for each property, due to different client’s reporting needs, the same expense category may have different accounting codes which the bookkeeper uses. The data base should be able to read the property name or number, the type of task performed and assign the correct account code to the billing.
At this time work orders are not utilized. The database should be designed to incorporate work orders in the future.
The data base should produce the following reports:
1) Summary of Maintenance Techs’ hours for payroll calculation.
2) An invoice, detailing the services performed, number of hours for each task and the total hours spent per month on service calls to the property, times the service billing rate (an hour rate).
3) A summary report for each property listing the total hours spent each month for each category of tasks, with the proper accounting code assigned to the total for each category.
4) A detail for each property of dates, tasks and time which would be in the format of a maintenance log.
Aside from the fact that I want my techs to be able to access their input screens from the web, the existing MSAccess template program would work for us with a few tweaks. We need to rename several fields and add others to the tables. The reports are also close to what we need. The switchboard is also quite close to what I envision.
Also in reviewing my specs we also need to track maintenance supplies for each property, similarly to how “Parts” are handled in the template.
Currently this information is consolidated and inputed monthly in a spreadsheet format which would be of assistance in designing the tables and reports.
