Development Case Study: University Thesis Database: Microsoft Access, SharePoint, Word and Excel

Development Case Study: University Thesis Database: Microsoft Access, SharePoint, Word and Excel

An Application Development Team Case Study:

Olas Logo

University Thesis Database: Microsoft Access, SharePoint, Word and Excel

Technology: Microsoft Access, Microsoft SharePoint, Microsoft Word and Microsoft Excel

Client: One of Ireland’s Top Universities
Year: 2020

As part of this redevelopment project, Olas breathed new life into an older MS Access application that had evolved internally in the university over a large number of years. The Thesis database was a central repository for recording students, examiners and Thesis data that then would be used in reporting on and creating all external typed letters from the department.

A key incentive for this project to get the go-ahead was the need to find a new home for the database as the traditional file-server networked drive that it sat on was now being decommissioned in favour of newer cloud based technology. What was now once a standalone MS Access application was now redesigned as a MS Access front-end application (screens and functions) with the data migrated to a SharePoint Lists back-end setup. This new setup was successfully achieved with no loss to performance or function.

In redeveloping the solution, we gave it a new improved interface, search features, security and reporting to MS Excel and MS Word based mail merge functions to create letters. We firstly began by streamlined the system by removing obsolete elements and to redesign the setup of the system to make it much more usable and maintainable for the users going forward. The other key part of this project was to introduce a new, detailed, custom mail merge facility. The new setup led to the creation of 35 separate MS Word letter templates which then would be called upon by the systems custom mail merge process, which would further populate the more individual elements of the letters. These generated letters could in turn be saved as MS Word document, as PDF’s, as email attachments or even as the body of an email itself. For a single student or examiner record it could be the case that the system could generate a single email with up to five separate generated documents attached. The setup of the letter templates as MS Word documents has meant the letters are extremely easy to update and format as required by the users going forward, or to even add new letters to the catalogue of documents that is populated by the system.

The end deliverable was a hugely successful migration of an old MS Access database with a soon to be made legacy environment setup, to that of a new setup utilising a SharePoint backend. While also providing a much needed modernised look and feel with an extensive mail merge facility.

About Us - Olas Team Member - Fergal

FERGAL O’CONNOR
SENIOR TECHNICAL CONSULTANT


Mobile: +353 86 841 3708
Email: fergal.oconnor@olas.ie


LIMS Tools for Breweries and Distilleries

Laboratory Beakers

These days’ laboratory testing forms an essential part of the brewing process; whether it is to drive for in-house quality, to ensure local/international drink standards, or to meet fair trade and regulatory compliance standards. With tests completed on everything from the raw material received, samples taken at various stages of the brewing process, finished batches, sensory tests and on returns/complaints.

Continue reading

Pharma Batch Tracker Reporting Tools

Pharma Batch Tracker Reporting Tools

In the pharmaceutical industry, Quality Assurance (QA) is essential for ensuring that products are manufactured and delivered to a safe and consistent standard, adhering to all guidelines, standards and stringent regulatory requirements set upon them. A challenge for all Pharmaceuticals is then how to effectively manage the whole Quality Assurance process as smoothly as possible whilst operating across multiple teams and departments; with the end goal of releasing as many batches on time and right first time.

Continue reading

Mail Merge Systems

Mail Merge Systems

BUILDING A BESPOKE MAIL MERGE SYSTEM?

One of the most common requests to our software development department is to deliver a system with bespoke advanced mail merge functionality. This might be to develop a brand new solution or to develop an extension to an existing tool!

WHAT IS A MAIL MERGE?

A mail merge is an automated process used to create a batch of documents/emails which are personalised for each record/recipient. A process that would otherwise take a huge volume of time to create manually can instead be completed rapidly at the click of a few buttons.

CAN I JUST USE MICROSOFT WORD’S INBUILT MAIL MERGE FUNCTIONS?

There are of course mail merge functions built into products such as MS Word that will suit for simple mail merge processes but when the requirements become a little bit more detailed or specific, you will find you need to start quickly looking elsewhere for solutions.

Typically, the systems we deliver have a focus on creating mail merge emails with a number of mail merge attached files added to them. These automated solutions are developed using either MS Access or MS Excel (with VBA Coding elements) as the central platform for the tool. MS Word is most often utilised for the creation of the source templates to be used by the tool (These templates may go on to become the body of the email, an attached MS Word doc or an attached pdf).

OVERVIEW OF COMMON FEATURES IN MAIL MERGE SYSTEMS

SAMPLE MAIL MERGE SYSTEM SCREENS

In the following sample system screen the user has selected a mail merge process that will first create the body of an email, before attaching two mail merge documents, a MS Excel file and MS Word Form. Also attached are two further static attachment files. The mail merge process will be addressed to the employee but their manager will also be cc’d in on it.

Upon clicking the ‘Start Mail Merge Process’ button, the process would cycle through each applicable record in the datasource, creating the emails and adding the attachments automatically.

Mail Merge Systems

Once a record is processed, details can then be recorded to a transaction history log.

Mail Merge Systems

On an Administration screen a list of all applicable basic Placeholder mail merge fields would be listed along with formatting instructions for those elements. More complicated mail merge features, such as embedding custom charts or tables of data in the document, would be handled separately to this method.

Mail Merge Systems

It is these Placeholder fields (encapsulated between the chevrons) that are then available for the user to use in the creation of the mail merge MS Word template files.

Mail Merge Systems

WOULD YOU LIKE TO ENQUIRE ABOUT HOW OLAS CAN DEVELOP A MAIL MERGE SOLUTION FOR YOU?

To learn how our development can help design and develop a mail merge system tailored to your exact needs, just send an email to development@olas.ie and we will be back in touch promptly to arrange a call.

To find out more about our Application Development teams services, click HERE

Author: Fergal O’Connor, Senior Training Consultant, Application Development Department.

CALL US FOR A FREE CONSULTATION WITH SOMEONE WHO SPEAKS YOUR LANGUAGE

About Us - Olas Team Member - Fergal

Fergal O'Connor

Senior Technical Consultant


fergal.oconnor@olas.ie
Mobile: +353 86 841 3708

Development Case Study: Accounts Control Spreadsheet Tool for a Hotel Chain: Microsoft Excel and Sage Financials

Olas Logo

An Application Development Team Case Study:

Olas Logo

Accounts Control Spreadsheet Tool for a Hotel Chain: Microsoft Excel and Sage Financials

Technology: Microsoft Excel
Client: A large hotel chain with a wide variety of properties and amenities
Year: 2020

Our client was putting together a management pack of tools and processes that they were rolling out across all their hotels in their Irish operation. As part of this suite of tools was the envisaged Account Control Spreadsheet Tool.

Their central accounts team had put together the core of a very powerful custom made accounting tool, constructed within a MS Excel spreadsheet file. This file was pulling data from a huge number of different resources, such as account extracts from Sage, Bank Statements, Trial Balances and Credit Card statements. They were then using this Control Spreadsheet Tool to help both report on and to balance their accounts on both a daily and monthly bases. However, they faced a huge problem with the file! Although it was operational from a functional perspective it was not usable from a user’s point of view!

The construction of the file was so complex that there were left with thousands of external links to other files. These links were both pulling in data directly into cells but also used as part of formula and look-ups. The result of all this was a MS Excel file that was unmanageable from a user’s perspective as it would take an age just to load. Every time Excel then needed to perform a calculation (which is very often) it would hang. This waiting time could also end up being multiplied further in instances where other MS Excel files may have been open at the same time.

In delivering a solution Olas not only addressed these key performance issues but also brought some great extra benefits to the project. Transforming it from a spreadsheet based file to that of a fully-fledged MS Excel based application.

The following is a summary of tasks Olas sought to complete as part of this project:

 

To achieve these goals, Olas sought to take an inventive approach which made use of a daily import process through VBA Coding, which in only a matter of seconds would bulk import data from 64 external files in a single go. Day sheets would then be created from hidden template sheets contained in the file for each day in the month. When rolling over to the next day, all calculations from the previous day would be stored as values and the 64 source data import sheets would be cleared out and replaced with the new day’s data. This meant that at any one time the file would only contain raw source data and formula relating to just the current day; thus massively reducing the amount of formula required in the file at any one time. This approach proved extremely successful with the performance of the file going from frustratingly unusable to incredibly quick.

About Us - Olas Team Member - Fergal

FERGAL O’CONNOR
SENIOR TECHNICAL CONSULTANT


Mobile: +353 86 841 3708
Email: fergal.oconnor@olas.ie


Development Case Study: Global Commercial Capability, Distributor Assessment Tool: Microsoft Excel

Development Case Study: University Thesis Database: Microsoft Access, SharePoint, Word and Excel

An Application Development Team Case Study:

Olas Logo

Global Commercial Capability, Distributor Assessment Tool: Microsoft Excel

Technology: Microsoft Excel
Client: A large multinational personal healthcare products and consumer goods manufacturer and distributor
Year: 2020

This project was headed up by our clients Global Commercial Capability team located in the UK but overlooking their 1,200 plus operations in multiple sites across Asia and Africa. The goal was to create a global standardised tool that could be used by the local markets to complete assessments upon their operations. The results of which could then be consolidated and reported regional and indeed globally.

As the tool was to be rolled out primarily across Asia and African locations the tool needed to be able to be available offline, easy to use, intuitive, multi lingual and not be to technologically complicated or resource heavy. For this task MS Excel was identified as the ideal tool for this project.

The project consisted of two main elements, a MS Excel Distributor Assessment file to be completed by each distributor and a MS Excel Consolidator Reporting file, used to bulk import and report upon the returned completed assessments. The project was further complicated by the need for the need to customise the assessment templates text regionally and also to include the ability to translate both tools text into different languages.

The assessment itself consisted of 45 Standards divided out across 6 Platforms, with standards either categorised as General, Commercial, Finance, HR or Supply Chain. In completing a standard, the distributor would be presented with a leading statement, from which they would need to select from a choice of four statements the statement that best represented their operation. A facility was also included to allow for the addition of evidence to support the chosen statement. Upon completion of a full assessment, the assessment would become locked down and the distributor would be given a score (Gold, Silver, Bronze). With the assessment completed, now open to them in the tool would be a printable Scorecard reporting how they performed in each Platform and Standard. Also an Action Plan, generated detailing what actions they need to take for each Standard in order to move up to the next level.

This completed assessment would now also be shared up to regional and global teams for consolidated reporting. Using a separate MS Excel based Consolidator tool, the completed assessments could be bulk imported and merged in the Consolidator tool, with regional/global pivot chart reporting outputted.

The end result was a highly portable and adaptable application that will become a key tool in assessing and managing the many global distributors for years to come.

About Us - Olas Team Member - Fergal

FERGAL O’CONNOR
SENIOR TECHNICAL CONSULTANT


Mobile: +353 86 841 3708
Email: fergal.oconnor@olas.ie