Doran, Nicholas Website URL: www.acsu.buffalo.edu/~janetliu
Hellwig, Adam Due Date: 12/7/12
Kenrick Corporation: Project Write Up
Kenrick Corporation manages over three hundred community homes, where
they provide ground services and external maintenance to these homes. As of right
now Kenrick uses a manual process when it comes to fixing miscellaneous repairs
that homeowners submit. Our group is building a database that will organize these
work orders and allow Kenrick to allocate jobs accordingly. This will allow the
supervisor to assign different jobs to different employees. Most importantly it would
organize the pile of work orders, which would ultimately increase job production.
The database was built off of the relationship of four tables. These tables
include job orders, employees, job log and buildings. The job log table was used as
an intersection table to connect the job orders table and the employee table. We
used these tables to make queries, which helped us make our reports at the end. Our
database consists of four queries. One of our queries called, employee’s for job
orders allows the user to find all of the high urgency jobs and who they are
performed bye. Another query that was built called, employee by ID number which
the user can use to select each employee’s information just by entering their ID
number. Our database also consists of multiple forms. We created a job orders form
that includes an employee ID sub form. This form shows the job itself, the location,
the urgency, and the employee who performs the job with their pay rate. Macros
were also included in our database. We have a help macro that when the user clicks
on it a message appears with a contact number. A close database macro was built so
the end user can click on it to close the macro. In total our database has four tables,
four queries, eight forms, eight reports, and five macros.
Recently Kenrick Corporation is looking to expand to become a multi facet
management company. When this time comes the database will have to be
enhanced. New records of employees and homeowners will have to be added. Also
the jobs that will be performed on these other properties will have to be added to
the database. As more properties are added new relationships will have to be
established for the database to still be effective. In the meantime information on the
database that we created will have to be updated if an employee is hired or fired.
Also if new homeowners move in than their records will have to be added.
With any technological system there is a chance that it could crash which
could result in a loss of business. For our database we recommend that Kenrick
uploads this to all of their private servers. Also this database will be uploaded to
external flash drives that can be used in case of an emergency. The more places that
this database is saved the better. For maintenance purposes the business expert,
Nicholas Doran, can update the database with current information. He can also teach
the supervisor how the database works so that the supervisor will be able to
allocate jobs effectively.
Report Write Ups
The first report we have created is ‘Buildings by Lot’. This is an important
report because each of the buildings Kenrick services is grouped into five lots in the
same housing development. This report creates business value because it allows
Kenrick to schedule jobs in the same lot number consecutively. This saves gas since
the transportation vehicles do not have to bounce around from lot to lot, and also
saves time because less is needed to transition between jobs.
Our second report is ‘Employees by Job Title’. This report illustrates the
chain of command at Kenrick. Viewing the employees by job title allows for easier
scheduling because it is simple to find which position is needed for a particular job,
and then make an available selection from the report. Another feature of this report
is that the mobile phone number for each employee is listed. This makes it quick
and easy to reach any Kenrick employee, and also directs you to whom you need to
speak to in the Kenrick chain of command for certain issues.
The third report is ‘High Urgency by Lot’. Shown in this report are all of the
jobs of high urgency, grouped by lot. This report creates business value in multiple
ways. One is that high urgency jobs are prioritized to be completed first. This
report only shows those jobs, so that Kenrick can focus on the most urgent tasks at
hand. A second is that the high urgency jobs are grouped by lot. This works in the
same way our second report does by saving gas and time, but only includes the high
urgency jobs that need to be completed first.
Our fourth report is ‘High Urgency Employee Cost’. This report allows us to
allocate the cost of high urgency jobs to each employee. It multiplies the employees
pay rate times the hours to complete the job to get the total cost of the job for each
employee. With this information, management can weigh the benefit each employee
provides to the project against the cost to have the employee on the job. The total
on the bottom provides us with all employee costs for all high urgency jobs which
helps management compare that cost with other costs associated with the projects.
The fifth report we have created is ‘High Urgency General’. It shows where
each employee should be for all high urgency jobs to help management keep
everyone on task and maintain the general scheme of labor at Kenrick Corporation.
The job type column can show which types of jobs generally carry more urgency.
Our sixth report is ‘Job Orders by Time received’. This report is important
because while Kenrick prioritizes jobs with High, Medium, or Low urgency; jobs are
also completed on a first come-first serve basis. With this report Kenrick can see
what exact day the job order was received, as well as the job type. This report is
necessary for scheduling purposes, especially when employee vacation time is taken
The seventh report is ‘Job Orders by Lot’. It creates the same business value
as the second report in terms of saving time and gas by grouping by lot, but also
encompasses the date received, time required to be completed, and date to be
completed. This allows Kenrick Corporation to actually make the work schedule
feasible, while still taking building location into account by grouping job orders by
lot. For example, when multiple job orders can be completed in one day, Kenrick
would schedule them consecutively if they were in the same lot to create optimal
The eighth and final report is ‘Job Orders by Time Required’. This report
groups each individual job order by the time required it will take to complete them.
It is a key report for scheduling purposes since it is based on the actual time Kenrick
needs for a job. Knowing which jobs take a long time versus a short time allows for
the creation of a schedule that also takes into account urgency of the job and when
the job order was received, and can be fit into working hours.