Custom Database Design & Development

Most systems we create require data to be stored somewhere. Usually we use a database. As a database development company we often use Microsoft Access for this purpose. So we are also Access consultants and do custom database design. You may need some database help in general or some specific Access help.

When building custom IT solutions, a question we ask potential customers early on is "Does your requirement exist already?" Occasionally they are surprised as they hadn't thought of looking, but more often than not the answer is "Our requirements are too specific to us" or "We've had a look and whilst the one we found could probably do what we want, it also does a lot of what we don't want - and it isn't cheap!"

This comparision may help you decide which direction to take.

Here are some screen shots of custom built systems.

Here are some more examples of features customers have asked for that we have created. They may give you ideas of what can be done:

Web based custom business system

The client has 2 main office locations but also some staff that work from home. They all need to use the data for different purposes so a web based system is ideal for them. Different job functions get different parts of the system made available to them via role based security.

The system comprises data entry screens, wizards for complex processes, reports that can be viewed on screen as well as exported to Excel, an invoicing feature which produces pdf's of the invoices and a dashbord including dynamic charts.

We host the system for the client and use asp.net and SQL Server.

Customer Surveys

Word icon

I know… we all hate them! But the customers customer said they needed a measure and as they say, the customer is always right!

The customer didn’t want a web site or web page, this was going to be done as an attachment to an email on a monthly basis. The difficulty was the rules they applied to determine if one of their customers was due to be sent a survey. The customers needed to be between 6 months and 3 years old, never have had a survey before and only for certain product purchases. It sounds relatively straightforward, but our customer tried to create something themselves using Excel and pretty soon realised Excel wouldn’t be able to cope! The list was about 30,000 rows of items purchased and still in warranty. So it wasn’t 1 row per customer.

Our solution consisted of 3 buttons. One to upload the list of purchased items and one to generate a spreadsheet of customers that were due a survey. The third allowed a list previously created to be regenerated. Behind the scenes we used some code to upload the Excel file into a SQL Server table, some logic built into SQL Server stored procedures, some code to pull out the results and generate a new spreadsheet. Other checks included ensuring an email address existed and that we hadn’t tried sending a message to it beforehand and it failed.


Generating emails from data

Outlook icon

Instead of creating emails manually in Outlook we can integrate this facility into your application. The email can be pre-populated with data from the application and either edited by the user before it’s sent or just sent without the user seeing it. The user does not use Outlook to create these emails. Frequently it is very useful to be able to see these emails in Outlook’s Sent Items. Most systems do not do this. We can integrate emailing features such as this into your custom built software.

Our customer wanted a completely automated way of reminding their staff to book themselves on a regular mandatory Health and Safety refresher course. Our solution runs overnight and generates personalised emails to those people who are due to attend the refresher course in the next couple of months. As well as this we implemented a similar system to generate an email to course attendees on the night following their training. Each email contains a link to a personalised course evaluation form they need to complete. If the delegate "forgets" to complete it, another email is generated 3 days later. All of the data collected in the evaluations is reported against in another web application.


Working with API's

An alternative to downloading files of data is to pull a stream of data and work with that. Many online services now expose an api (application programming interface). When you understand what they can provide and how you ask for it you request some data and process the returned stream. We find that that is normally the easy bit! It’s how you handle that incoming stream (usually in json format) that takes the effort. This is particularly so when you request time-based data. Normally the api lets you specific a start date and time. The difficulty is handling duplicated data. To further automate the process we set up automated pulling of the data from the api and running our processing of the incoming json stream.
Our customer needed to get data from their business partner via an api (internet connection that bypasses a web browser). Users have an internal web page to process the new data.


Excel / CSV processing

Excel icon

The only usable interface to an application our customer was using was by importing or exporting csv files. This is a common requirement, importing data from files exported from other systems in csv format. Once you get past a few thousand lines, processing line by line becomes very time consuming, so we used SqlBulkCopy to pull the exported data into the SQL Server database that our application was using. Then there were all sorts of processes such as does the customer already exist, if not, create one and link it to the order, has the contract expired, if so update, and many more checks and potential changes.
Our customer processes a file of 100,000 rows every morning and on a weekly basis processes files of customer (40,000 rows) and contract information that is associated with the daily event file. We produce reports of anomalies that our customer can then investigate. We made this a “manual” button pushing process initially to give control, but are about to automate it further by detecting when a new csv file arrives and running the process automatically at that point.


SMS automation (text messaging)

Did you know that it's possible to set up a system to send text messages (SMS) via your computer? We added this feature to an application we built that made bookings. Our customer wanted to reduce the amount of "no shows" and offer an extra service to their customers. The day before their appointment, the system produces a list of customers that are due to visit. The user selects which customers to send a message to and clicks OK! Their customers receive a text reminding them of their appointment. The advantage of texting is that it is unobtrusive, better than a phone call and easy to do. It's actually relatively easy for us to include this feature in your application.


Brochure production

Word icon

An unusual request we had was for a system to generate a brochure that was going to be professionally printed and bound by a printing company. It was very important that the user couldn't tweak the finished product before printing as any errors at this stage would be costly. We decided to use Microsoft Word as a platform for the output. Access manages the data. After the (very nice looking) layout was designed we then needed to output the data from the application which stores the data into the template. As well as words there are photographs and charts that need inserting. Using Word development skills, our system resizes and compresses them appropriately. When the document is complete the user can save it as a pdf to give to the printer.


The system evolved to enable users to produce customised subsets of the whole brochure.



phone logo
Call 01604 879505 to discuss your requirements
email logo
enquiries@blackmanassociates.co.uk
asp.net developer
Access developer
Excel developer
VBA developer
Web developer
Database developer
SSRS developer
Northampton
Milton Keynes
Herts
Bucks
Beds
Birmingham
UK Wide
Worldwide

Based in Northampton in the UK and working with companies worldwide.

© 2020 - Blackman Associates Ltd.