Originally posted Friday, 27 March 2015
Written by William “Wes” Stewart
Build a Custom Database Using MS Access
In 2009, the Facilities Project Management Office (PMO) at UTHealth made the leap from using MS Excel to MS Access to manage project cost information. Although the initial focus was project cost accounting, during development and later after implementation, many other benefits were realized and the database evolved into a comprehensive Project Management Information System (PMIS).
The UTHealth PMO consists of a Director, five Project Managers and one Project Specialist whose primary function is cost accounting. The group manages all renovations on campus from $0 to $4,000,000. There are typically 100 active projects at any given time. Project Managers are “fee for service,” meaning salaries are paid from the projects they manage. Fees are collected on all projects and are calculated on a sliding scale by multiplying the anticipated total project cost (TPC) by percentages ranging from 3 to 8 percent.
Prior to constructing the database, the PMO used Excel for project cost accounting and reporting. Project managers were responsible for keeping a project budget spreadsheet (standardized format) up to date. A separate master spreadsheet was maintained on the department server, which was also updated by the project managers. Separate standardized procurement forms were used for purchase order requisitions and change orders. Invoices were typically not tracked electronically except in the university Financial Management System (FMS). Outside the PMO, the Facilities Accounting Office maintained separate project spreadsheets that were used to calculate fee collections and assist with capitalization of projects over $100,000. Most times the accounting spreadsheets did not match the project manager’s spreadsheets for the following reasons:
- Project managers updated budget worksheets periodically instead of real time and not all purchases were properly accounted for
- Accounting and the PMO had different project numbering systems and there was an inconsistency in project naming
- Accounting was primarily interested in projects over $100,000 that could be capitalized. Fees were often not collected on smaller projects
- Other spreadsheets used included accounting-code reference sheets, cash flow (fee collection) forecasting, lessons learned, vendor list, executive summaries and various other Ad Hoc reports. The result was hundreds of different spreadsheets and forms, residing in different locations that contain redundant, conflicting or incomplete information. Extracting accurate, meaningful data was difficult and time consuming, especially when staffing changes occurred
Specific concerns to the PMO are fee collection and financial reporting to customers and upper management. If a customer or upper management requests a project budget update, it does not reflect well if it takes the PMO several days to update a spreadsheet and respond or if a conflicting report is sent from Accounting. At the end of each project, the customer receives a reconciled budget/cost summary worksheet showing all project expenses, along with the fees that are due. It is important to the reputation of the PMO that this final report is accurate and error free.
It is also important not to overcharge customers and set rates only high enough so that revenue is equal to salaries plus overhead at the end of each fiscal year (FY). Overages cannot be carried forward. Significant overages (or shortages) raise concern from upper management. Therefore, rates need to be evaluated annually using a projected cash flow report and adjusted as necessary at the beginning of each FY to meet this goal. Using Excel with different versions floating around and applying the sliding scale to each project made these and other cost accounting tasks cumbersome and difficult to achieve.
Searching for a better solution, off-the-shelf commercial databases were considered. We found very good companies that specialize in PMIS. Some advantages of these packages are that a considerable amount of time and effort went into the design and testing, so the databases would probably have very few programming errors. It is also a fairly quick solution, since it has already been developed generically, and professional programmers are available to assist with implementation. The specific products we reviewed focused on a wide range of project management functions, including scheduling, resource management, document storage, and online collaboration.
In our opinion, this type of software is ideal for large capital projects, where process and procedure is duplicated on each project but was more than what we needed to manage small day-to-day renovations. With cost accounting being our primary focus and speed being of essence, we wanted a system that would make our processes more efficient and less cumbersome to the project managers. We did not want to require them to input additional data that would be relevant to large projects, but did not bring significant value to small projects. Also, forms, reports, accounting codes, and fee structure had to match our specific requirements so after the initial software purchase there would have still been a significant re-programming and maintenance cost.
Ultimately, with budget being the driving factor, we decided a small customized database residing on the university server, which in-house staff could maintain, would be sufficient to address our needs. MS Access software was selected primarily because it was readily available as part of the MS Office Suite, so there was no need to purchase additional software. Also, due to the simplified user interface, developing a database using Access does not require an advanced education in computer programming. Although an understanding of basic database-development principles is essential, these can be self-taught using books and online tutorials or learned in one or two college semesters. Development of an “efficient” database does require intimate knowledge of the system requirements and existing business processes, so exactly what is needed can be delivered. Constructing the database in-house offers the opportunity for significant cost savings and can better produce the desired results since the in-house staff knows exactly what is needed.
While it may be helpful to hire a consultant to assist with programming, this database was developed by the Director and the Project Specialist responsible for accounting, both non-IT professionals. Since in-house staff built the database, much of the very time consuming (but absolutely necessary) “needs requirement” phase was expedited, and the project quickly moved into the conceptual design phase. Conceptual design primarily consists of developing a list of tables (similar to spreadsheets) that are needed to capture the desired information and a plan to link the tables together so information between the tables can be shared. The table/relationship in Figure 1 shows most of the data that UTHealth decided to store in the database and how the tables are linked together.
At first glance, this may seem complex, but there are only two primary tables. One is the project table that contains detailed information pertaining to the project, and the other is the purchase order table where most of the project cost information is stored. Other tables are basically sub-tables that hold information pertaining to one of the two primary tables. These tables reduce input requirements and are used to develop time saving drop down menus in various forms.
Using drop down menus assists with accurate reporting, decreases data input time, and reduces errors. All tables are linked together by a primary key or unique data field common in both tables. We selected four unique IDs, the project ID, purchase order ID, vendor ID and chart field (FMS funding code) ID. Once all the tables are linked, a single form can update many tables at the same time and one report can be produced using information from many tables. Administrative effort and the possibility of conflicting reports is significantly reduced since data is only entered one time in the database, instead of in numerous spreadsheets. All of the old forms and spreadsheets that were being used are no longer necessary. The database is stored on the PMO’s central server and backed up daily by the IT department. Authorized users can quickly add and retrieve information and unlike an Excel spreadsheet, multiple users can add, delete, and edit at the same time.
Figure 2 shows an example of the usefulness of linking tables. Notice how the purchase order form shows or is being used to update information in the purchase order, change order, invoice, and vendor tables. Additionally, the footer automatically calculates totals at the bottom. Now, when entering changes orders and pay applications the user can quickly check the accuracy of the contractor’s paperwork.
After completion of tables and relations, forms and reports were developed. System testing was accomplished using sample project information to insure all of the forms and reports worked properly. After testing, project data was either imported from various Excel spreadsheets or manually inputted. Then user training and implementation took place. From start to finish the project took about four months. The only cost associated was the in-house hours it took to learn Access and develop the database. However, that payback was substantial, since outsourcing is not required to make modifications.
Of course, a database is only as good as the information put into it. A key factor to the success of this project was the ability to produce purchase order requisition forms and change order forms (actually reports in database terminology) that provided the same information and looked similar to the standard procurement forms. Once this was accomplished, the standard procedure is to produce these documents using the database. This accomplishes several objectives:
- Ensures the database is up-to-date and real time, since it is used to produce the procurement requisition forms
- Increases efficiency by pulling exiting information from other tables needed to produce the form and by only having to input the same data in one place instead of both the request form and budget worksheet
- Increases accuracy by automatically calculating totals
Upon completion of this project, hundreds of spreadsheets containing duplicate information stored in numerous locations are now consolidated into one centralized database that is updated in real time. Cost estimating has become faster and more efficient since accurate information is readily available. Project budgets and expenditures are now tracked in real time, allowing up-to–date, accurate reporting at the click of a mouse, without asking the project manager to “please update the spreadsheet.” Partly due to increased accuracy, the PMO now manages its own fee collections, so the separate accounting spreadsheets are no longer necessary. The only other project accounting system is FMS, where a separate account is set up for capital projects. While the FMS serves as the university accounting system, its usefulness as a PMIS to the PMO is limited to producing actual transactions and reconciliation.
Below is a list of uses and reports now available in the PMO database:
- Develop, maintain, and report project feasibility estimates, baseline budgets and cost summary updates
- Prepare and print purchase-order requisitions and change order forms to be processed by procurement
- Access to a consolidated list of vendors by trade with contact information, HUB status, existing contracts available for use, and in-house performance ratings
Various administrative reports, such as purchase order requisition status, procurement processing turnaround times, projects to be closed, invoice payments due, and open PO’s that need closed
- Baseline to actual schedule and budget reports used to determine the percentage of projects delivered on time, under budget
- Lessons learned by project type, vendor or building, and a list of all change orders that occurred on a particular project—a critical component to evaluating lessons learned
- Cash flow summary or project fee revenue forecasting
- Parametric and analogist cost estimating based on historical data
- Executive project status reports that can be filtered by school, building, department, funding codes, etc (see Figure 3)
The UTHealh PMO wanted to share the successful implementation of this project and to demonstrate that it is possible for non-IT professionals to successfully implement a custom database or PMIS system using (readily available) MS Access software. Five years after completion, the database now contains accurate, detailed information pertaining to nearly 1000 renovation projects. As more project information is stored in the database, the more accurate cost estimating becomes. Valuable information from lessons learned has become readily available as well as more vendor contacts and vendor performance information. Information is not lost due to personnel changes. Check and balance reports against FMS are readily available to help insure accurate closeout reconciliation, fee collection and forecasting.
Using spreadsheets alone as a PMIS can result in hundreds of different spreadsheets and forms, all residing in different locations that contain redundant, conflicting or incomplete information.
- A good database consolidates nearly all information, forms, and reports in one location; allows immediate access to accurate, up-to-date information; and historically archives final data.
- If developed correctly a PMIS will reduce administrative effort by pulling existing information from multiple tables.
- Microsoft Access has a user-friendly interface and is an excellent tool to develop a PMIS. Since it is part of the Microsoft Office Suite, there are no additional software requirements and it interacts well with other Microsoft applications.
- Developing a database in house allows total ownership. Modifications or development of new forms and reports can be made quickly at no additional cost. There are many step by step manuals and online references to assist with MS Access.
William (Wes) Stewart has been the Director of Project Management at UTHealth for 9 years. He holds Master degrees in both Business Administration and Construction Management, a Bachelor of Science in Business Information System and the PMP certification from the Project Management Institute. He can be reached for questions at William.email@example.com.