AX Medical

by Kurt Rodenhizer, MS, MBA
 
AX Medical is a company that specializes in the engineering and development of medical devices. Their customers are mainly medical manufacturers who outsource to AX for design engineering contracts.

Challenges:
AX has several offices throughout the US, and their customer contact information had become a collection of files from several sources. The objective was to create one master database for all their contacts, remove duplications and then start making updates to one data source.

Diagnosis:
ePD was contracted to manage the creation of an offline database (built in MS Access). The objective was to create a database that would allow for the effective categorization, search and edit capabilities for all key contacts and to produce numerous variations of print ready reports on demand.

Solutions

Master Table
ePD collected all the records into one table, removed double entries and assigned customer numbers to each client office. The number of records was reduced from 30,000 to 9,000 following the data scrubbing.

Standardization Process:
Data scrubbing included standardizing the way information is presented in the database (e.g. VP vs. Vice-President; First Street vs 1st Street). This tedious work was essential so the filters (search and queries) set for department, job titles, office locations, etc., would work reliably.

Master Form:
Once the tables were ready, a master form was organized to allow for record location and editing. A linked sub-table was used to provide details on all contacts working for a specific client office. Drop downs and checkboxes were inserted whenever possible to reduce the time associated with data entry and editing.

Filter-based Reports:
Numerous report templates were set up and a software code was used to display in the print ready reports, only what was filtered (queried) from the “filter” page. For example and as shown in the image below, a filter was applied for key accounts, and then by sub-division. The report shows only the filtered data. On a subsequent filter, the user can select contacts in just the purchasing departments and in a specific territories.

Results:
1. The custom database was completed in five days including the data processing.
2. The database allows for hundreds of filtering or query options (e.g. by job title, department, territory, sales volume, etc)
3. Whatever is filtered remains in queue as the user clicks on the templated reports (allows for hundreds of report options without needing to design hundreds of reports)