Home Page     Contact Dave Thompson Consulting     Site Map

     Microsoft® Access Project Examples

Dave Thompson Consulting

    408 Rose Drive    Allen, TX 75002  (Dallas  /  Ft. Worth, Texas)   Tel: 972-727-5670

Microsoft® Access and Office Custom Programming and Consulting Services

Rev. October 19, 2019

 

Microsoft Access is a desktop, relational database system, with underlying development language (VBA), suitable for automating business processes from single-user to complex, multi-user information systems. First released in 1992, MS Access offers a stable, powerful development tool for business applications with defined upscale migration paths to enterprise-level solutions such as Microsoft SQL Server.

Development cycles – from concept to implementation – are relatively short for Access applications, typically one to six months. Short development cycles imply less budget impact (four to five figure costs).

Below are descriptions of some of the MS Access projects that I have done for both small and large businesses. Note the wide variety of business processes that can be automated with MS Access.

Advertising - Media event, Microsoft Access database application (newspaper inserts) for national retailer.

Airline Technology Group - Incident tracking application for software development and maintenance.

Air Charter Crew Duty Log - Track crew duty data of flight crew for FAA compliance and payroll tracking.

Banking - Custom database software for private placements of corporate bonds.

Commercial Real Estate - Data import, clean-up, and build custom mail list with individualized comparison data.

Commercial Real Estate Finance - Track equity and lender sources for commercial real estate finance company.

Communications Operations Center - Multi-user, Access database for circuit information.

Employee Review Process - Annual employee review process with passwords and upstream supervisor access.

Financial Planning - Multi-user, client information system for financial planning firm.

Fixed Capital Appropriation -  Track appropriations, purchase orders, and vendor invoices at division level for national retailer.

Healthcare - Provider credentialing database software.

Home Building and Related Services - Annual tax analysis and reporting across companies and groups at corporate level.

International Hotel Company - Promotions search, Management reporting with currency conversions.

International Hotel Company - Blackout dates entry by category, multi-year view, lookup hotel by code, name, or location. 

Legal - Client information application for class action, litigation firm.

Manufacturing Schedule and Downtime Tracking - Measure and improve the utilization (uptime) of manufacturing process (concrete pipe). Set and track daily production schedule and capture downtime events and time to resolve.

Manufacturers Rep Firm - Monthly commission reporting for multiple rep groups representing manufacturers' lines across multiple retail locations with commission structure by location.

Medical - Prescription lookup and printing with patient script history.

Medical - E/R   - Quick lookup database for MD's and community resources with log file for contact notes.

Management Consulting Questionnaire - Multi-part input with results analysis and reporting.

Quote Program - Electronics Company - Multi-user, quote program. 

Timesheets - Non Profit Organization - Allocate timesheet hours against funding sources in non profit.

Workflow Process (Graphical Interface) - Electronics Distributor - Multi-user, project tracking with notes. 

 

 

 

========================

Advertising Group for National Retailer (Irving, TX)

Problem/need: Data was being managed by outside vendors, and reporting was slow and expensive.

Project Implementation: Track and manage schedules, rates, and quantities of newspaper advertising inserts for the advertising department of a national, multi-store retail chain. Matches newspapers, markets, and stores for several hundred stores (40+ states). Print insertion orders for newspapers, and production run lists for commercial printers. Track advertising rates for more than 200 newspapers.

Project outcome: Reports available immediately. New reports can be designed by in-house staff. Shortened cycle time to plan media events, and shortened lead time to change an existing event plan. Faster and more reliable reconciliation of  newspaper billing.

Top of page

 

========================

Airline Technology Group (Ft Worth, TX)

Problem/need: Implement a software application to control incident tracking and resolution process.


Project Implementation: Access, multi-user database application that  implements and enforces business rules for process. Provide management reports of activities, problem areas, and summary reporting. Workgroup security to ensure process integrity and provide audit trail.

Top of page

 

========================

Air Charter Crew Duty Logs (Addison, TX)

Problem/need: Track cockpit crew on/off duty cycles and rest periods to comply with FAA regulations and for internal payroll use.

Project Implementation: Both PIC (pilot in command) and SIC (second in command) duty on/off are entered for each trip. Program calculates rest periods (last duty off to next duty on), total on-duty time per cycle, and other parameters such as flight hours, instrument flight hours and count of day/night landings.

When trip is scheduled and crew assigned, display rest period and alert scheduler to short rest periods. When trip is completed, enter crew data. Reporting includes cumulative on-duty times and flight times per crew member on monthly, year-to-date, or user selected time frame.

Top of page

 

 

========================

Banking Private Placement Investment Group (Dallas, TX)

Problem/need: Migrate existing large scale (legacy) information system to desktop system using Microsoft Access. 

Project Implementation: Multi-user, MS Access application to track the private placement of corporate securities for global finance group of bank. Provide search of available placement inventory by a wide variety of criteria. Legacy system data imported into Access. Implement workgroup security for management control.

Top of page

========================

Commercial Real Estate - Direct Mail Campaign (Dallas, TX)

Problem/need: Prepare direct mail campaign source data for custom mail campaign.

Project Implementation: Import 15K records into MS Access database. Separate fields as needed (such City, State, Zip from one field into three), and clean-up data (capitalization, spelling. format Zip field from 9 char to Zip +4). Develop custom embedded search algorithm to process each of 5K records (target audience) against database (15K records) for geographical location criteria (among others) and build output table to drive direct mail merge process.

Top of page

========================

Commercial Real Estate Finance (Dallas, TX)

Problem/need: Manage list of lenders and equity sources to include contact information and parameters of sources such as industry specializations, geographical areas of focus, and typical deal size.

Project Implementation: Import records into MS Access 2007 database from lists built in Excel.  Clean up data and distribute into separate tables (companies, contacts, etc.) following relational database schema.

Build user interface (UI) to manage lists and contacts, to allow setting of company attributes such as qualified, not qualified, indicate primary company contact. Build UI to support search and filter list by user provided criteria such as geographical focus, deal size, and industry specialization. Support contact notes. Multi-user, MS Access 2007 application.

Top of page

 

 

========================

Communications Operations Center (Allen, TX)

Problem/need: Communications center, 24x7 operation, manages a large number of communications circuits (trouble shooting and setting up new circuits). The center deals with a variety of equipment vendors, telephone companies, other outside entities in support of the center's  mission. Access was chosen as a proof-of-concept model for database schema design, business rule enforcement, and audit trail procedures. Longer range goal to upsize project to Microsoft SQL Server to support large, geographically distributed user community. 

Project Implementation: Multi-user application for customer and circuit information. Lookup and drill down by customer ID, Circuit ID, etc. Related circuit details (modems, IP information, etc.) drill down from circuit; comments log for each circuit record; automatic date/time stamping of record changes..

Top of page


========================

Annual Employee Review Process (Dallas area)

Problem/need: Annual review process of professional employees by their managers; collaborate on past year review and goal-setting for the coming year. Security so that each employee has access only to their own review form, but managers (at multiple levels) have access to all reviews in their reporting path (direct reports and downstream employees). Reviews can be edited by the employee, or the employee's direct-report manager only.

Project Implementation: Multi-user, custom MS Access application with workgroup security. Individual users sign on and set their own personal password. When a user logs on, the program determines if the user is a manager with downstream employees, and - if so - provides a list for drill-down to appropriate review forms. Users (other than managers) are restricted to viewing only the contents of their own review. 

Support for admin user to reset forgotten passwords, change reporting relationships, or change employee job assignments that might occur during the review cycle.

Top of page

 

 

========================

Financial Planning Services Company (Dallas, TX)

Problem/need: Existing system was paper-based, labor-intensive, and slow.

Project Implementation: Multi-user, MS Access client information system. Track client personal information and services provided to client. Extensive use of drill-down techniques to display related information for individual clients. Fast navigation to client records to permit answering telephone inquiries during the first call with a goal of eliminating call-backs.

>>Examples from this application <

 

Top of page

 

========================

FCA - Fixed Capital Appropriations (Division Level, National Chain)

Problem/need: Manage, by location, fixed capital appropriations (improvements, equipment/fixture additions) at the division level for existing stores.

Project Implementation: Multi-user, MS Access. Create fixed capital appropriation (FCA). Enter, print, and track purchase orders for each FCA . Enter vendor invoices as received. Match invoices to specific purchase orders for comparison. Track spending by assest class, breakout vendor freight and tax charges, and compare FCA dollar commitments against invoice totals. Robust database search capabilities: store location number, vendor, purchase order number.

NOTE: Access application has been adopted and is in-use at four regional divisions representing several thousand store locations.

 

 

Top of page

 

========================

Healthcare - Credentialing (National)

Problem/need: Meet both regulatory and corporate requirements for credentialing providers (doctors). Existing system was paper-based and difficult to audit.

Project Implementation: Developed a new multi-user, software product from concept to commercial release as independent, solo developer for start-up software company. Product supported the tracking of data elements and sequenced processes with dates and audit information. Access program included export functionality for sending specific data sets to Microsoft Word for Windows for mail merge operations. Also, the application also has an export feature for accounting data to interface with existing, legacy accounting system.  

Top of page

========================

Home Building and Related Services - Corporate Tax Planning (Dallas, TX)

Problem/need: Corporate tax group does annual federal and state tax analysis and planning. Needed to convert legacy, PC-based software to multi-user, current software.

Project Implementation: Using financial algorithms from old software, and report examples, develop a true rendition in MS Access with relational model and currency data types for accuracy. Numbers shown as thousands, and rounding algorithm with VBA code procedures provide consolidated reports that can be reconciled with individual company reports. Increased functionality over legacy software to support multi-user and enhanced ad hoc reporting. 

Corporate structure is groups and companies. Reporting is available for individual companies and groups. Additionally,  reports can be assembled by combining companies (either within a group or across groups) and printing ad hoc group reports for analysis. At the top level, groups can be selected to print consolidated reports, or any combination of companies can be selected to form an ad hoc, top level consolidated report.

Provide batch report printing for hard copy history.

Top of page

 

 

========================

Legal (Dallas, TX)

Problem/need: Input work histories and related data elements to multi-user information system for class-action, litigation firm. Provide reporting both for internal analysis and for court filings. 

Project Implementation: Multi-user Access database with workgroup security and event tracking. User interface designed to present intuitive interface for a complex set of data elements and relationships. Client's IT department handled multi-user network issues so that the application could be concurrently used by users on home office. primary local area network (LAN) and by LAN users at remote offices in several states.

Top of page

========================

Manufacturers Rep Organization (Addison, TX)

Problem/need: Implement new system of commission reporting based on a combination of manual entry of manufacturers invoices and point-of-sale (POS) data by selling location by uniform product code (UPC). Import POS UPC data downloaded from retail group. Calculate monthly group commissions by selling location, by manufacturer with a multi-tier commission structure across several thousand locations.

Project Implementation: Design and implement a relational database structure using MS Access to reflect multiple selling groups carrying multiple lines calling with a varied commission pay rates. Design and implement front, menu driven, user interface to support the processes: data import from ASCII txt files, manual data entry of some invoices, maintain  selling location table, maintain group assignments to selling locations by manufacturer line by commission rate. Generate monthly commission payment reports using the data structure. Support history function both by group summary reports by month, and data mining by maintaining a history file of line item UPC's (selling location, quantity, month sold).

 

Top of page

 

========================

Manufacturing: Production and Downtime Tracking (National)

  Problem/need: Machine utilization efficiency drives production output and unit cost. To measure and improve efficiency, daily production goals (a function of machine setup configuration) are needed. Downtime events need to be detailed along with the impact on production such as downtime minutes. The data from the floor (production and downtime events) should be presented both as text-based reports and as graphs for analysis and management action.

Project Implementation: The client was currently tracking data through spreadsheets. A multi-user Access application was developed to replace the spreadsheet process. The primary function of the application is to:

Set daily production goals based on the equipment model and the day's production mix (machine configuration).

Track downtime events by equipment and issue type. Capture both production time lost and time-to-repair. Allow for event comments both by machine operator and maintenance personnel.

Reports available for standardized date ranges (30, 60, 90, 180 days) or user-entered date range.

Configure program interface based on user group logon (floor, office, reports only, admin).

Project methodology: The application was developed over several months. One plant was selected for development and testing. The project was driven by a single-point client contact serving as project manager. The final application was the result of several development versions used by the test plant with their feedback used to refine and reach the final version.

Project outcome: The application has been rolled out nationally.

Top of page

 

 

========================

Medical - Prescription Writing and Patient RX History (Dallas, TX)

 

Problem/need: Busy E/R needed software to quickly locate individual scripts from a standard list of prescriptions, print the Rx for doc signature, and automatically update patient Rx history file.

Project Implementation: MS Access program with fast, efficient script lookup Existing scripts in the database can be individually edited as issued and a decision made whether to add the edited script to the standard list or leave the standard list unchanged. The database stores a copy of the script exactly as issued as part of the patient Rx history along with date/time and doc name.

Outcomes:  Quality of service improvements. Many fewer pharmacist calls regarding legibility issues. Online availability of patient Rx history for review and possible consultation. SIG field usually more detailed than hand written scripts.

 

Top of page

 

 

========================

Medical - E/R (Dallas, TX)

Problem/need: Quick lookup database for medical staff, referral, and community resource information. 

Project Implementation: MS Access database used by docs and E/R staff to find contact information for local agencies and staff physicians. Lookup by name or specialty. Drill down on address and phone list (office, beeper, fax, cell, etc.). Ability to record associated contact notes (automatic date/time stamp).

>>Examples from this application <

Top of page

 

 

========================

International Hotel Company 

Top of page

========================

International Hotel Company 

Blackout Dates Entry and Lookup

Track the process of requesting forms from participating hotels to capture blackout dates and promotion program participation. 

Multi-user entry/lookup of blackout dates with monthly, multi-year display of results by category with drill-down for data entry.

Quick lookup for individual hotel by code, name, or location.

Top of page

 

 

 

========================

Management Consulting Questionnaire

MS Access application for multi-part questionnaire entry. Data results are organized by person, department, and organization. Answers are processed by client-provided, proprietary algorithm and presented in various report formats. Question types supported in the application include; binary choice, multiple choice, ranking, and free text entry.

Questionnaire - with results -  is used by management consultant as part of a team-building process.

Top of page

========================

Timesheets - Non Profit Organization (Fort Worth, TX)

Enter bi-weekly, timesheet hours (multiple locations) for volunteer and employee professionals and staff. Charge hours against specific funds or funding sources and allow entry for leave time. Assign billing rate to each employee/volunteer. Track and record for each timesheet the employee/volunteer role such as attorney, paralegal, or staff.

Various reporting such as employee timesheets, hours per pay period by location, allocation of hours and assigned costs (hours x rate) per funding source code. Allow reporting for one pay period, or for multiple pay periods (date range).

Provide report sub-totals (such as by location) and report totals (all locations). For internal use, track earned FICA and retirement amounts.

 

Top of page

 

========================

Quote Program (Garland, TX)

Multi-user program to assemble and price quotes for electronics company. Lookup items by classification, allow outside purchase items, and price labor items by classification such as engineering and test time. Price the quote with markups by groups (internal items, outside purchase, labor).

Provide financial analysis reports for internal use, and final quote with optional, customized notes for each quotation. Allow final quote to be printed, or launch email program with quote as attachment.

Provide multi-field search screen to lookup quotes already in the database.

Export/Import functionality was provided to support quotes made in the field by sales reps using laptops. Field sales personnel can export quotes from laptop, and send to home office as email attachment for incorporation into the main database.

Top of page

 

 

 

========================

Workflow Process (Graphical Interface) - Electronics Distributor (Richardson, TX)

 

Problem/need: Client designed work flow process (Visio diagram) for tracking new projects with a defined set of technology areas. Needed was a multi-user implementation that permits collaboration by application engineers to make suggestion and comment on solutions for each technology area. Also, the new application should trigger an email alert to distribution (application team) when a new project is entered. Notes by individual contributors on a project should be audit stamped (date/time/username).

For each technology area, users can suggest vendors for that area, status of vendor interaction, and engineer actions for that item such as customer visit, email, phone call.

All interactions are presented from most recent interaction to oldest. A summary report is also available for each technology area.

Project Implementation: Multi-user, MS Access application with program mdb on each users laptop and backend data file on central server with VPN access.  Using client's Visio block diagram, create form to match Visio diagram with drill down capability on each of fourteen technology areas. By changing background colors, indicate which areas have activity and which areas are not applicable for project board chosen (from Main Menu: Customer, Project, Board). Users log on to application with username and password.

Results: Application is in production at several locations.

Additional Comments: This is a case where the client had fully developed idea, to include the user interface, and needed a developer to implement his ideas. Microsoft® Access with its multi-user relational database foundation, a complete underlying programming language (VBA), and excellent form design capability, was an effective and efficient solution for this application.

 

Top of page

Dave Thompson Consulting - Home Page