Access Projects
I now have another website that covers Microsoft Access Development in more detail, you can view it here Freelance Access DeveloperBelow are a selection of projects I have undertaken utilising Microsoft Access 97, 2000, 2003, 2007, 2010. I have found Access to have almost unlimited flexibility due to it's use of VBA (Visual Basic for Applications)
This system consists of a database created by the client using Microsoft Access 2007, the client approached me to incorporate VBA programming to accomplish tasks that were not possible without programming.
Main Menu
Some of the tasks undertaken were automatic popup of selection forms to promote data consistency, programming of form elements to ensure only logical selection can be made.
Driver Details form
This form holds details about each driver, as well as car details to monitor such things as insurance renewals
Job Sheet form
This form provides facilities for the recording and monitoring of Jobs, showing details of the selected job as well as sub sections showing which jobs have been allocated and those that are currently unallocated.
Report Selection Criteria
This is a multi-faceted form I created to collect report selection criteria prior to running reports, one form contains all the programming to offer the relevant options to the user dependant on which report they have selected to run.
A typical report
This is the output from a typical report, the report contain data selected via the report selection criteria form above.
Client: 121 Fundraising Limited
As the name implies 121 Fundraising Limited provides a range of services to charities to assist in their individual donor fundraising activities. They specialise in the use of games to encourage participation in fundraising by commonly untapped market segments, whose cause disposition alone may not persuade them to give.
In early 2006 they launched a numbers based game designed to replicate the key characteristics of a weekly lottery, but structured in such a fashion as to offer a far greater degree of flexibility and better prize offer than normally feasible and at no risk to their charity clients.
Players in the game can win cash prizes ranging from £10 to £50,000 every week, based on the comparison of numbers between their own selection and an external range. The game was designed to rely upon the number match probability in order to predict the prize payout ratio.
Early in 2009 it became apparent that the distribution of player selected numbers was not even across the range of permutations (of which there are 49 million), hence certain number combinations could potentially result in multiple jackpot liabilities in a single week.
A Different Approach
I took a different approach to the problem and decided to compare users selected draw numbers to determine where there could be multiple jackpot liabilities.
Driven from a single form, the user operates from left to right; firstly undertaking analysis of all the currently selected draw numbers, the system provides user feedback during this process. Once the analysis is complete the central section of the form shows the results of the last analysis.
Reporting
The system provides flexible reporting facilities allowing the user to select, a full report, a report against a given set of draw numbers, a report based on the number of jackpot wins, and another based on the financial levels of payout.
Example Report
Export of Results
The system also provides a facility to export the results of the latest analysis to allow further comparison to data exports from another system.
To his great credit Dennis rapidly grasped the fundamentals of the problem: part of the conundrum is that to identify the high risk combinations using a conventional approach would have required a very substantial amount of processing power and a long time to run, even on a powerful server / database facility; impractical in the context of a weekly draw.
Dennis's solution is both simple and elegant, with an Access 2003 application taking a data feed from our main system. Results are available in a variety of formats and flavours and enable us to see rapidly where corrective action is needed.
I would add that costs are very reasonable and flagged in advance, meaning no unpleasant surprises. Anyone looking for an intelligent, value-for-money and friendly approach to application development can expect to be very pleased with their choice when appointing Dennis McDermott to the task.
Clive Mollett
Director
121 Fundraising Limited.
Client: ABS Wastewater Technology Ltd.
Service Order Management Scheduler
The prime purpose of the system was to record details of Standish Metal Treatment 'jobs'.
Prime areas of operation:
- Import of information from Excel files, with user feedback.
- Storage and reporting of Orders, Schedules and Completion information
- One-click display of Order Details
- One-click display of Purchase Orders
- Easy administration of lookup data to ensure data integrity
Main form
Order Details form
We had a very urgent need for an internal database system to schedule our service orders for a key customer. Following several discussions with local developers, nobody was able to meet our very tight deadline.
Finally following an internet search we found Dennis's website and made contact with him. After a brief chat and an emailed specification of what we needed, Dennis was able to confirm very quickly that he could complete the MS Access based system within our time demands and at a reasonable cost.
Despite no face to face meetings and all communication being made by phone and email, Dennis was able to exactly meet our design specification. The delivery was on time and the system worked faultlessly from day one.
We were very impressed with the professionalism and level of service provided, even under less than ideal conditions, which would have put off many suppliers.
Clive Patten
Managing Director
ABS Wastewater Technology Ltd.
Client: Metal Cleaning UK Ltd.
Job Tracking System
The prime purpose of the system was to record details of metal cleaning 'jobs'.
Prime areas of operation:
- Creation of Purchase Orders
- Recording of Enquiries/Contracts
- Production of Quotations
- Production of Job Programme Sheets
- Storage and reporting of Quality Control measurement data
- Storage and reporting of Paint Thickness Measurements
- Production of Advice/Delivery notes
- Production of Invoices
Main Menu
Enquiries/Contracts form
Job Programmes form
An example output, an Inspection Report.
I initially contacted Dennis to update an old Access 97 system. Dennis completed the work on time and well within budget but still more work was required to turn the old system into a workable database.
Following Dennis's recommendations we started afresh using access 2003 designing a new system far superior to the basic model and capable of much, much more. Explaining the step by step process of a shot blasting and painting operation is not very easy. Dennis soaked up the information, I cannot think of a time when I had to explain a process more than once. It isn't rocket science I know, but it can be pretty complicated at times; the dry film thickness formulas and relationships between coating application, times and climatic conditions etc. All have to be understood prior to programming.
Dennis set certain criteria for information to be entered correctly and added live 'popups' that total financial category data in real time, a real aid when ordering against budgets. The reports produced are brilliant, everyone at both Metal Cleaning UK and Standish Metal Treatment are very pleased with the results so far.
I say so far because I am continually asking Dennis for improvements which are done professionally, on time and to requirement. I cannot recommend him enough, fantastic service.
If you would like to contact me personally for a reference, you can use any of the contact emails or telephone numbers on the MCUK or SMT websites - Steve Brierley QA / H&S Manager.
Client: Rail Professional Development
Delegate tracking database
I was approached by Rail Professional Development, a company involved in rail safety to provide training to their staff, this training took the form of undertaking the development of this system alongside RDP staff. This enabled the training to be focussed and at the end of the training the customer had a useful working system.
The system was written in Access 2002, and runs on a server to provide centralised access to the data throughout the organisation.
- Features multi logon levels to allow different functionality for different users.
- As part of the training the following topics were covered.
- Table design
- Form design
- Basic query writing
- Menu customisation
- VBA programming
- Functions and Procedures
- Naming conventions
- Properties, Events and Methods
- Defensive programming
- Control structures
Time tracking database
Following on from the Delegate tracking database system, Rail Professional Development asked me to develop a Time tracking system for them.
This system allows each project the company undertakes to be entered into a database, storing many details about the project, but most importantly the available time to completion.
The system provides facilities to store working hours per consultant against the various projects monitored, and provides extremely flexible reporting facilities. For example reports can be produced that show tasks undertaken by each consultant on each project for a particular client or across all clients, within a selected date range. The system also produces productivity reports against each consultant, this again can be produced to cover a specific period.
Key Features
- Recording of time against, tasks, projects and clients.
- Dynamic highlighting of any project overruns.
- Productivity reporting down to consultant level.
- Fast feedback reporting to clients.
Client: Bruce Burlington & Co. Ltd.
Bruce Burlington & Co. Ltd. are a wholesale wines, beers and spirits wholesaler, they commissioned me to create a Pricelist Production System.
This system was written in Microsoft Access 2000 and Visual Dialogscript.
The system produces pricelists for the client, the data for the pricelists is held in a UNIX system, which produces fixed width textual output files, these files are imported into the Pricelist Production System which provides extensive facilities to customise the pricelists produced.
The pricelists are printed with user selectable images for product group as well as user entered blocks of descriptive text.
The Menu
Pricelist customisation facilities:
- Set the descriptions for Departments, Product groups and Individual Products.
- Manage pricing schemes.
- Set the display order of Departments, Product groups and Individual Products.
- Assign various elements of additional data per product to be displayed on the pricelist.
- Activate, De-Activate Departments, Product groups and Individual Products.
- Mark products for highlighting on the pricelists.
In addition the system provides comprehensive management reporting facilities.
Product Overview
Pricelist Customisation
Pricelist Data Selection
Client: Canon Consumer Imaging
This system was developed in Microsoft Access 2000 to hold details of all of Canon Consumer Imaging's clients. The system has very versatile reporting facilities allowing the client to produce bespoke reports at the click of a button. The system also provides the facility to output data in CSV (Comma Separated Value also known as Comma Delimited) files for use in Microsoft Excel.
Client: Broadway Records
Stage 1
Stage 1 of this project provided the client with facilities to import item catalogues from Excel, modify this data by calculating the selling prices, and create comma delimited files for upload to Amazon.
Stage 2
The second stage of this project added increased functionality to the system.
- Importing data downloads from Amazon relating to products sold.
- Providing the ability for the client to set up e-mail templates in three languages, English, French and German.
- Sending of dynamic e-mails created using data stored in the database at various stages, such as when goods are dispatched, or placing orders with suppliers.
- Processing orders by scanning the barcodes on the stock as it arrives from suppliers, incrementing stock levels appropriately.
- Batch printing of despatch labels, including the facility to select a starting label thereby reducing label wastage.
- Creating upload files, to upload to Amazon in three currencies, GPB, Euros and US$, facility included for client to maintain currency conversion rates.
The Orders Form
The Order Processing Form
Client: Dartford Council
Dartford Council approached me to develop a system that would navigate a directory structure seeking Microsoft Word and Excel files. Upon finding such files would set the custom properties of the file based on its location within the file system.
The purpose of this exercise was to gather data for use with the councils SharePoint services.
The application was developed in Microsoft Access and uses calls to the Windows API (Application Programming Interface).
After processing the user if offered the option of viewing a process history to enable quick cross referencing of the files processed.
Client: Excel IT Ltd.
Works Request Database System
I was approached by Excel IT Ltd. to undertake development of a works requests database system, the company were using Excel to track thier work requests and had realised that a 'proper' database was required.
The system was written in Access 2003, and runs on a server to provide centralised access to the data throught the organisation.
- Holds data relating to engineers, suppliers and customers.
- Uses the above stored data to provide engineer, supplier and customer information to the main works requests data table.
- Produces small order works request with a single click.
- Includes comprehensive report selection criteria.
- Dynamically calculates profit/loss in both monetary and percentage terms.
Client: Moving Ahead Online Ltd (MHOL)
MHOL contacted me to discuss the potential for creating a system that would synchronise data held on their local servers with their award winning website.
After an initial meeting and discussion relating to other data synchronisation systems I had written I was engaged by MHOL to undertake the development of a system.
MHOL were manually updating their website with conveyancing details and as their business was growing this was becoming impractical. The company decided to install a proprietary case management system. My task was to synchronise the data within the in-house case management system with the website.
I created specification documents that covered the interaction between the in-house case management system and the synchronisation system, as well as between the synchronisation system and the existing website. I created the system using Microsoft Access, and also utilised IIS to provide a platform for the ASP pages that are involved in the synchronisation process.
The system compares data in multiple data tables, and adds, updates or deletes data on the website as appropriate, the system also integrates with pre-existing code on the website that sends HTML based e-mails to both clients and agents detailing the progress of the conveyancing process. The system was written so as to be autonomous in that it did not require any changes to the existing website source files.
System Features
- Easy configuration.
- Data availability checking.
- Configurable synchronisation period.
- Configurable data and website location information.
- Provides feedback to user of both local synchronisation analyses and online synchronisation progress.
- Stores self maintaining process history.
Client: MSL Medical
Customer Database System:
This system was created for the client in Microsoft Access 2000.
The system enables the client to store all their customer records, detailing sales, invoices and returned items for reporting purposes.
Client: Pepperneck
Pepperneck, a design agency approached me to undertake enhancements to one of their clients Access Database applications.
I have completed a number of small projects for their client, Filtermist. The most recent was the creation of a portable Access application to allow field staff to have up to date information regarding their many installations.
Pop-up Search Facility.
Located installation information with a small photograph.
Larger photograph showing more detail.
Client: Sea Containers
Database modifications
Sea Containers had an Access database in use in their Madrid office that required extensive updating to enable it's full potential.
This additional data allowed the creation of many new reports which provide the client with far more useful information than was previously possible.
Client: South Essex Gymnastics Club
This project involved the enhancement of the South Essex Gymnastics Clubs existing Access 97 Database system to provide facilities to allow staff to record payments in real time.
In addition to recording payments, the enhancements provided the following:
- The production of a printed receipt.
- The ability to take payments for various activities, or equipment purchases.
- Tracking and display of a members payment history.
- The ability to make adjustments.
- The ability to assign charges to attendees of a particular training session on mass.
- The production of invoices.
- Various management reports.
The Payments Processing Form
Client: South & East Essex Training Group
I have designed and developed a training course administration system for this client:
The system was developed under Microsoft Access 2002, also known as Access XP.
It's key features are:
- Data Tables to hold the following information
- Clients
- Instructors
- Courses
- Invoices
- Printing of Course Notification paperwork for Client and Instructor.
- Intelligent Address Label printing allowing the user to select a specific label to print on.
- Invoice Printing.
- Invoice Reporting.
- Bulk or singular Course Attendance Certificate printing.
Client: 301 Design
Wisley Golf Club Data Synchronisation
This project was written in Microsoft Access 2000 for The Wisley Golf Club. The system compares and synchronises
data held in local data tables with web site data, the comparisons are undertaken using SQL (Structured Query Language), while the synchronisation is achieved without the use of FTP (File Transfer Protocol), employing ASP (Active Server Pages) and a simple client interface which is truly 'Click and Go'
Regent Carriage Services Ltd is a successful mini cab company operating in South Kensington. During December 2006 we expanded our business three fold and the old processes of manually writing the logging of jobs, dispatching, reporting and invoicing was getting out of control and needed a serious overhaul. There were so many readymade database systems on the market, however none of them were suited to all of our needs and requirements, so we decided to design our own custom database.
This can be a daunting task at the best of times; however we were very lucky to have chosen Dennis to program and enhance our database system. From the very first conversion we had with Dennis regarding our needs we were confident that he had grasped an excellent understanding of our requirements for the database. His understanding of our business needs through his questioning lead to an excellent customised database.
Since we have been working with Dennis we have found him to be thoroughly professional. His work output is of an excellent quality, spot on; on every occasion. His helpful suggestions on enhancements to our database have produced a product that we are absolutely delighted with. To anyone looking for programmer I could not recommend Dennis highly enough.
Jawad Saleem
Regent Carriage Services Ltd