Creation of a Grants Database Highly Customized for College Level Reporting

Volume XLIX, Number 2
Julie H. Oestreich
University of Findlay
Kimberly K. Heersche
University of Kentucky

Project Context

The University of Kentucky—a public, land grant university with around 30,000 students and 2,000 faculty—receives over $300 million per year in extramural research funding. The College of Pharmacy supports over 60 faculty who submit to diverse sponsors including the National Institutes of Health (NIH), Department of Defense, National Science Foundation (NSF), state agencies, non-profit foundations, and industry. The research office handles pre-award activities at the college level separate from, yet integrated with, centralized research administration on campus. Personnel consists of two faculty administrators (associate and assistant deans), one director, one college grants officer, and an administrative coordinator.

For reporting requests, the office previously collected data from multiple sources requiring significant effort to integrate and analyze. Examples of requests that proved complicated to fulfill included: 1) proportional award credit for collaborative research, 2) sponsor success rates at the college level, and 3) funding partnerships with other departments, universities, foundations, and industry. University systems supported internal approval and award management, but did not integrate all grant-related information in an efficient and convenient way for department level needs.

Similar to the University of Kentucky, many other universities prioritize information technology (IT) resources for the critical compliance requirements associated with post-award financial management. Specifically, 64% of research-intensive universities leverage the same enterprise system for post-award and general ledger activities (Saas & Kemp, 2017). In the pre-award setting, however, only 8% of the same institutions possess systems that combine general ledger and pre-award activities. To bridge this gap, a majority of institutions purchased a commercial product specific for pre-award needs, 8% built in-house systems, and 13% still process manually (Saas & Kemp, 2017). Nonetheless, few of these technology solutions fully integrate all information, which impedes efficiency and hinders operations when research administrators encounter complicated requests (Saas & Kemp, 2017).

Furthermore, research administration needs vary at the university and department levels (Hughes, 2004), and available systems do not capture all of the department-level preferences for pre-award tracking and reporting. The lack of broad and integrated resources creates difficulties for colleges and units interested in compiling data for fine-tuned, faculty-level metrics that assign proportional credit for collaborations. As interdisciplinary research constitutes a major goal for universities and sponsors, the emphasis on quantifying multiple principal investigator (MPI) and co-investigator contributions continues to increase in importance (Joiner, 2009). The percentage of MPI proposals submitted to NIH increased by 50% from 2010 to 2013 (Rockey, 2014), and the number of collaborative projects at NSF matches those from single investigators (NSF, 2017). Thus, the quantity of reporting requests for complex research metrics likely will continue.

New System Considerations

To accommodate reporting challenges, the office pursued a more sophisticated system. To meet this objective the following priorities were identified: 1) low cost, 2) customized to internal processes, and 3) optimized for reporting. Furthermore, the office preferred to own and manage the solution to ensure time sensitive requests did not require dependence on outside support.

Based on these criteria, the group weighed several options. At first, the office hoped to improve their system of numerous spreadsheets because Excel® is easy to manipulate and has flexible calculations. They also considered commercial products with strong user interfaces and customer support. However, both of these options possessed substantial limitations or cost constraints, leading the office to review the advantages and disadvantages of Microsoft Access®.

At the University of Kentucky, building an Access® database added no further costs as the university already supported the Microsoft Office® Suite and a database consultant with relevant expertise. The college IT unit provided a stable, on-site server capable of hosting the database. The office also valued the ability to create a highly customized and inexpensive system.

As described by Snyder and colleagues, most Access® databases lack technical controls to enforce data management best practices such as security, audit trails, and uniform quality control. In addition, specialized functionality such as automated processing, integration of external data, and management of metadata is often absent (Snyder et al., 2012). Based on the small size of the office, the team considered many of the missing features non-essential and expected a highly customized product would balance the lengthy development process. After considering these factors, the group selected an Access® relational database as the best solution and pursued development (see Figure 1).

Product Development

The database was constructed collaboratively with three primary parties: 1) the college research office director, 2) a database consultant from campus Technology Training (part of Human Resources Training and Development), and 3) other members of the research office team, including a college grants officer and administrative support (see Figure 2).

The research office accomplished project milestones through a series of meetings with the database consultant and assigned work on their own. The initial planning phase of the process involved the input of the full group to gather all relevant perspectives, while the development team—consisting of the director and consultant—built the majority of the product. The consultant employed a coaching approach, so the office actively learned Access® functionality throughout the project. Therefore, knowledge transferred to the department, which ultimately reduced long-term reliance on the database consultant.


In order to maximize the flexibility and efficiency of reports, the group engaged in extensive process mapping to detail the grant process from application to award to closeout. The team considered all necessary data fields from existing systems, relationships between components, input and output needs, as well as user interface requirements. The meetings involved long, detailed discussions that benefited from the consultant’s pointed inquiries and translation of database best practices to the team. The pre-build process (approximately 10 hours in five meetings over a four-month period) proved critical to the overall design and completeness of the final product.


After carefully considering the system requirements, the group constructed the first build of the database over the next three months. Process workflow was translated into Access® logic through the creation of database objects, including 35 data storage tables and over two dozen relationships connecting these tables. The initial user interface for data entry was developed and then beta-tested by entering a handful of grants. Following the trial, the team finalized the user interface and proceeded with the live system at the beginning of the 2016 fiscal year.

The development team then expanded the user interface to include an advanced search feature and quick links to common reports. Over the next 10 months, they built over 50 queries and reports for fast retrieval of high priority data related to 1) upcoming proposals, 2) submissions, 3) awarded proposals, 4) study section status, 5) budget forecasting, and 6) current and pending support.

In the last major build phase, the development team dedicated two months to a new expenditure component. The group successfully created import and append features to integrate data from the financial portion of the university enterprise system (SAP HANA). The additions allowed advanced tracking of primary accounts and subaccounts, simplified reporting, and predicted indirect costs allocated to the unit.

Database Specifications

The completed database accommodated the complicated aspects of grants management through specific features including a split database format, customized forms, and standard queries and reports.

Split Database

The database was divided into two parts, defined by Access® programmers as the “back end” and “front end”. The back end stored all the data on a server with restricted access, while the front end housed the user interface on office desktops for optimized performance. The split database supported multiple concurrent users, decreased chances of corruption, and allowed all users to view and work with data in real time.

Another benefit of the split database was the opportunity to create different versions of the front end that all connect to the same back end data. For example, the development team created a read-only version, so interested parties could review grant information without inadvertently changing data. The office also incorporated new functionality and improvements through development copies of the front end, which were versioned and archived once adopted by the broader team (see Figure 3).

User Interface

The application opened to a switchboard form that displayed options to 1) view the main form with all data, 2) search for specific information, or 3) run reports. The main form, the primary point of interaction with the database, facilitated daily workflow by allowing office staff to view all information pertinent to each grant proposal. The main form featured a header area with primary data points and overlapping tabs that track proposals from preparation to close (see Figure 4).

The search button on the switchboard provided options to limit results by investigator and proposal status. A narrow list of proposals appeared with enough information to choose the specific proposal desired (see Figure 5). The end user could then navigate to the desired proposal displayed in the main form.

Data Input and Output

The college grants officer manually entered the majority of information. Expenditure data, however, were imported from the financial component of the university enterprise system to the database after some minor manipulation in Excel®. The office also developed a system for quality control where inputs were verified at scheduled intervals throughout the year.

The research office extracted data from the system using standard queries and reports designed for information frequently needed or requested. The ability to present information on demand in meetings reduced preparation time and assured up-to-date results. Custom reporting was accomplished with new objects or by adjusting existing queries and reports. When needed, the office exported queries to Excel® for further adjustments and refinement.

Project Evaluation

Two years after the database went live, the group reflected on the success of the new system and if it possessed enough value to remain in use. Overall, the new database met expectations for improving office capacity for reporting metrics—the primary purpose for its construction. In addition, the database provided other benefits beyond reporting within the office and across the university.

The database captured the complete profile for individual grants and faculty members, including roles as MPI, co-investigator, and mentor. As a result, the office provided broader and faster reporting with the ability to retrieve live data on demand from the database. For example, the office completed current and pending support for seven investigators in the same amount of time previously required for one investigator. For a separate annual report, the office formerly reviewed and checked multiple sources to assign proportional credit for MPI and co-investigator awards. With the database, the total workload decreased by one to two days, and the task was delegated due to the efficiency and ease of reporting with the database. Additionally, the organization of the data allowed new reporting capabilities, such as calculation of success rates.

The team realized additional benefits post build. Importantly, the relational database provided a visually cleaner and seamless experience for multiple users compared to the flat files of Excel®. The primary database user (grants officer) immediately recognized its value for daily work activities, especially the ability to view and track information in one convenient location.

Outside the research office, the project also fostered relationships throughout the university. Research groups from two healthcare colleges requested copies of the database structure. Preliminary activity suggests that the value widely transferred and saved time for the consultant and units. Since the database only required minor adjustments for both groups, they avoided the time dedicated to the planning and build phases. In this way, the database served a broader benefit to the university beyond its originally intended scope.

Lessons Learned

  1. Stay the course
    Similar to other major projects, we pursued changes beyond small, incremental steps and needed to build confidence in the process and maintain forward momentum (Eyerly, Forstmeier, & Killoren, 2000). Ultimately, our entire team supported the project, but encouragement and direction were critical in the planning phase when no tangible product was available. The consultant and director addressed concerns and provided assurances that final implementation would require demonstration of an effective product.

  2. Prioritize the planning phase
    Fortunately, the beta version of our database possessed no major issues. Extensive process mapping at the beginning of the project allowed us to avoid time-intensive corrections after database implementation. In our opinion, the pre-build process, though challenging at times, enabled a smooth transition to the live product and facilitated overall project success.

  3. Recruit the right people
    We maximized team contributions by setting clear expectations and defining roles at the start of the project. Specifically, the director served as team champion and motivated super user to maintain progression and foster buy-in from office staff. The research office team provided valuable perspectives and supported feasibility of implementation. The database consultant proved essential and served as facilitator, build consultant, and external advisor.

  4. Consider additional team benefits
    The database supplied better data management and reporting as intended. In addition, the project offered an intellectually rewarding opportunity for team building that led to process improvements and refined operating procedures. Employees also expanded technical skills and forged valuable relationships across the university.

Authors’ Notes

The authors submitted a disclosure for this database and presented a poster of this work at the 2016 Society of Research Administrators International Annual Meeting. This work was performed at the University of Kentucky when Dr. Oestreich served as Director of the College of Pharmacy Research Office.

Julie H. Oestreich, PharmD, PhD
Assistant Professor
College of Pharmacy
University of Findlay
1000 North Main Street
Findlay, OH 45840

Kimberly K. Heersche, MM
Information Technology Trainer
Training and Development
Human Resources
University of Kentucky

Correspondence concerning this article should be addressed to Julie H. Oestreich, College of Pharmacy, University of Findlay, 1000 North Main Street, Findlay, OH 45840,


Eyerly, R. W., Forstmeier, K. G., & Killoren, R. (2000). The long and winding road: The politics of building an ERA system. The Journal of Research Administration, 1(2), 5-11.

Hughes, M. A. (2004). Facilitating sponsor requests: A relational database model for project and effort reporting. Journal of Research Administration, 35(2), 31-38.

Joiner, K. A. (2009). Commentary: Evaluating faculty productivity in research: An interesting approach, but questions remain. Academic Medicine, 84(11), 1482-1484. doi: 10.1097/ACM.0b013e3181bb28a8.

National Science Foundation. (2017). Report to the National Science Board on the National Science Foundation’s merit review process, Fiscal Year 2016. Retrieved November 3, 2017, from

Rockey, S. (2014, July 11). How do multi-PI applications fare? [Blog post]. Extramural Nexus. National Institutes of Health. Retrieved from

Saas, T. & Kemp, J. (2017). It takes an eco-system: A review of the research administration technology landscape. Research Management Review, 22(1), 1-12. Retrieved from

Snyder, D. C., Epps, S., Beresford, H. F., Ennis, C., Levens, J. S., Woody, S. K., … Nahm, M. (2012). Research Management Team (RMT): A model for research support services at Duke University. Clinical and Translational Science, 5(6), 464-469. doi:10.1111/cts.12010


Departmental research administration, tracking and reporting, database