Database Samples

Interesting Projects We Have Done

Helpline Call System

Needs Met:

  • Find all of the records related to a particular caller or consumer during a request for help.
  • Share data with field offices
  • A more efficient and accurate system for creating reports than doing it by hand from their existing system.
  • Collect additional information such as demographics

Solution:
We created an online helpline system accessible to all staff and helpline volunteers, who can only see
records on a “as needed” basis.
The system cross references calls, the issues raised during a call and the people associated with those
calls and issues. The user can see all of the issues raised by or about a particular person. The user
enters information into the system directly as they are on the phone or at any other time that is
convenient for them.

Benefits:

  • Improves their ability to help individuals with their issues by using the system’s built in cross references and detailed individual records.
  • Collects demographic and issue data to improve grant proposals and outcome reports
  • Improves the efficiency of information collection to
    • Improve communication between affiliates, and between staff and volunteers
    • Reduce staff time producing grant proposals and outcome reports
    • Improve systems advocacy capabilities by identifying issue trends
  • Creates custom reports, charts and graphs to
    • Support public policy advocacy with data
    • Supply accurate information to funding sources
    • Create on-demand reports for your Board of Directors & membership
    • Explain NAMI services to members and the general community
  • Costs less money compared to networking local affiliate offices.

(PHP and MYSQL, Flash)

Physical Needs Assessment

Needs Met: A more efficient and accurate method to create detailed and summary budgets for multiple years based on individual work items. While Excel is a great tool for doing calculations, it can be easy to miss corrections on items repeated across many different rows. Each report must be hand created and saved, creating opportunities for errors and an inefficient process. Data must be re-entered and corrected each year to account for inflation.

A tool for planning repairs

 

Solution:
Create a relational database to store each of the needed repairs as a workcode and the time the
repair needs to be completed. Separates are used for the actual year the object is expected to need replacing versus the date the repair is scheduled. Each workcode has a related expected life span cost and cost
identified for each year so that if you change the cost of a workcode, it changes the cost all of
the related repair items. One of the system’s unique features is the ability to flexibly and simply adjust
the prices each year with inflation, without re-entering the data.

Benefits:

  • Improves the efficiency or producing budget reports and improves their accuracy by
    • Reducing errors from data entry and changing workcode costs
    • Ensuring that all pertinent data is collected in the report (The user does not have to look through the records and select each appropriate one)
  • Improves the effectiveness of maintenance plans so that the property is better managed and reduces the worry of having unexpected budget expenses.
  • Reduces the worry about maintaining the system by automatically creating new entries for the when a particular item is replaced

 

(Microsoft Access)

Environmental Reporting Systems

Needs Met: Prove compliance with EPA regulations while maximizing production when emissions are related to which product is being produced at the time, rather than to the total amount of product created.

Solution: A relational database allows users to enter pertinent process information on a daily basis to calculate emissions values on a regular basis and monitor performance against the EPA limits. A separate area allows the user to enter EPA’s accepted emission factors for the process to ensure that the calculations are made consistently all the time.

Benefits:

  • Provides accurate record keeping for EPA reports
  • Reduces regulatory burden by showing inspectors an effective and efficient compliance system in place
  • Helps maximize production schedules while complying with regulations by providing real-time analysis of emissions rates.
  • Helps retain or attract customers in industries in which environmental compliance records differentiates product

(Microsoft Access)

Time Keeping/Project Tasks Systems

Needs Met: An efficient system to record time spent on projects for accurate billing, project time analysis, employee incentive programs, and supervisory functions to ensure that a project is being completed efficiently.

The ability track the tasks for individual projects and record notes about the project.

Solution: Create a system that allows individuals to time their activities as they do them, enter them after the fact if they were away from the computer or were multi-tasking, and edit them for mistakes. Likewise, an accessible project task system facilitates communication between employees and supervisors about the various items that need to be accomplished. To improve efficiency the system exports the data to Quickbooks compatible file.

Benefits:

  • Improves billing rates and time use analysis since employees that track their billable items immediately enter more time than those that enter their time in weekly batches.
  • Improves billing efficiency by exporting a Quickbooks compatible file, saving the need to re-enter the data.
  • Improves project supervision by providing supervisors to review the amount of work done on a project and intervene, if necessary before a project gets way out of budget.
  • Improve communication about projects between employees and supervisors
  • Improves billing accuracy and efficiency
    • providing flexible views of the hours worked by employee, project, and billing status
    • declaring that certain workcodes cannot be billed to the client
    • allowing the user to decide whether to bill the remaining work items (for instance if the project was single price bid rather than an hourly project.)

(1 system Microsoft Access, 1 system in PHP and MYSQL)

Desktop Order Tracking System (Orders Completed in Excel)

Needs Met: An efficient way to record orders into an electronic system and then electronically communicate information about the orders to third system. The orders are delivered via Excel spreadsheets or via text file associated with an email.

Solution: Build a networked Access database that reads the excel order forms (or text files) and records the initial sale information into the database. Then make the data editable in Access and create a process that generates the necessary text files and ftp them to the main frame system.

Benefits: Improves efficiency and accuracy by reading the data automatically, rather than having staff input the information by hand and possibly miss ordered items.

Completes a vendor’s request that order information and updates be sent to them via FTP so that they can update employee reward points.

(Microsoft Access)

Online Small Group Attendance Forms

Needs Met:

  • Improve the chances that leaders will complete attendance forms in a timely manner.
  • Reduce the amount of time spent on tracking attendance.
  • Create a data set that allows you run a variety of reports

Solution: Provide an online system that leaders can login, create their meeting dates, add and delete people, change the roles of people at meetings, record attendance, and record notes about the meeting. An online back office area allows staff to download data, compile statistics, and notice trends.

Benefits:

Improves the likelihood that leaders will supply their attendance data by providing a convenient system that is available 24/7 to allow leaders to record attendance at their groups.

Reduces staff time spent recording attendance data since the leaders input the information directly into the system, rather than recording the information on paper and staff re-inputing the data into an electronic system

(PHP and MYSQL)

Association Website Tools

Needs Met:

  • Provide members a directory of addresses and phone numbers
  • Provide an easy way to keep the data up-to-date
  • Know what email addresses are associated with what people in a list serve
  • Identify people in the list serve or database for special emails.
  • Track private data about members or other people related to the organization.
    • giving records
    • dues payments
    • other activity involvement

Solution: Build an online database that allows members to update their information themselves at any time and that also tracks other private information as well. Provide tiered logins that keep appropriate data private. Provide an online back office that allows staff to flexibly select records for downloading into a mail merge document or for sending a series of emails.

Benefits: The system improves communication within the organization and improves the efficiency of maintaining the data.

(PHP and MYSQL)

Loan Application Informational Forms

Needs Met:

  • Provide a unified form for collecting loan information across multiple websites
  • Provide staff an easy way to view the data securely so that personal data is not compromised.
  • Provide email notifications to staff when someone completes one of these forms
  • Provide email notifications to a loan officer under certain circumstances
  • Provide administrative staff the ability to assign loans
  • Provide supervisory staff the ability to chart officer responsiveness to customer requests
  • Provide applicants with a courtesy email when they have completed the form
  • Provide management the number of leads generated by geographical region.

Solution: Create an secure online application with a behind-the-scenes decision tree to distribute the information and direct visitors back to their main website. Create a back office solution for coordinating staff to assign the forms to an appropriate office, and a related system for staff to be able to view the forms being sent to them.

Benefits:

Reduces the time required to gather the necessary information from the potential clients and allows the loan officer to be much better prepared on the first meeting.

Provides loan officers uniform information regardless of which site the client visits.

Improves responsiveness to customers by providing management tools to track how long it takes loan officers to respond to customers and take action when necessary.

(PHP and MYSQL)