Creating your Access Database System

Initial Access Database consulting
I would be pleased to offer an initial chat with you to determine if Access is the right product to fulfil your requirements, discuss problems you are having with current systems, and suggest solutions.

Requirements gathering (This consists of the following)

• Me gaining a complete understanding of your business and your requirements in respect of the database
  
  
• Discussing the things you need to store in the database
  
  
• Determining what you have at the moment and how this can be integrated if appropriate
  
  
• Discussing the functionality required
  
  
• Number of people expected to be using the system at the same time
  
  
• Any requirements for Data Synchronisation - Remote Access
  
  
• Do you already have Access, if so which version? Access Versions

Clive Patten, Managing Director, ABS Wastewater Technology Ltd. said "Despite having no face to face meetings and all communication being undertaken by telephone and email, Dennis was able to exactly meet our design specification."  See my testimonials

Structuring your data

• Determining the most efficient way of storing your data always considering how you want to present your data (for example what information you require on reports) It is very important to ensure that data that is required in your outputs is actually stored.
  
  
• Discussing the types of data you need to store, how much space to allocate to each data item, for example you may want to store e-mail addresses, its important to set aside enough space to store a long e-mail address without allowing too much space which is inefficient.
  
  
• Determining unique identifiers in your data, for example Invoice numbers, Order Number, Part Number, Customer numbers.

Trevor, Director GAP Photos Ltd. said "Dennis asks all the right questions and adopts a right-first-time approach."  See my testimonials

Referential Integrity

• This means ensuring that the system treats your data in a sensible way, for example if you wanted to delete a customer from your system its likely you would want to also delete all of that customers orders, otherwise you would have unrelated data sitting in your system doing nothing (this is known as orphaned data)

What is meant by Data Validation?

• This can take many forms, some simple and others much more complex, for example, the storing of your employees mobile telephone numbers (assuming an employees database is being constructed) would not be mandatory as not all employees would necessarily have a mobile telephone, or may not be happy supplying that information to their employer. Whereas, you would be likely to make the storing of 'next of kin' to be mandatory so that you would now who to contact in the case of an emergency.
  
  Steve Kelsey, Global Head of L & D, ESAB said "I have already recommended Dennis to others in my profession and will certainly be using him again. Great job!."  See my testimonials
  
  Another example may be that you were recording some measurements in a production environment and you knew that a particular measurement being entered would always be in the range 3-8 the system could ensure that users could not enter values out of range. This is also important when extracting data from your Access Database system, as it allows us to make assumptions that we know will be true, as an example if we were reporting on those measurements recorded above we would know they could only be between 3-8 as we had validated that during data entry.

Steve Brierley QA / H&S Manager, Metal Cleaning UK and Standish Metal Treatment said "Dennis set certain criteria for information to be entered correctly so we have nice clean data."  See my testimonials

Security

• I normally develop Access Database systems that only allow users access to the system by entering a username and password, this does not always apply in very small businesses. In larger organisations this can be implemented in such a way that different people within your organisation can be authorised to undertake different tasks in the database, for example, only accounts staff may be able to view and edit financial data, whereas some managers may be able to view and report on some financial data but not edit it in any way.

Backup

• This can take many forms, if you are a small organisation I can advise on and setup data backup methods that would allow backup to external disk drives, other PCs on your network or online backup. It makes sense to backup your data regularly, and it is always better to store these backups remotely from your main office, therefore it is advisable to backup to removable storage media such as an external disk drive, or directly to the internet.
  
  For the larger company I would expect you to have a backup strategy in place, and I could advise who ever looks after your current backups on how to ensure your database is backed up as well.

Mary Peskett Smith, Grants and Fellowships Manager, Paul Mellon Centre said "Dennis undertook the work quickly and to our great satisfaction. He is very approachable and reliable. I can thoroughly recommend Dennis and his expertise."  See my testimonials

Below is a brief summary of what you will see when using your database system
As an experienced Access Database designer I always design my Access Database Systems as bespoke systems; they only display items that are used in the system, rather that displaying the whole of the Access user interface. This helps ensure that my systems are easy to use without all the confusing clutter of 'raw' Access.

This also ensures that users do not inadvertently wander off into areas of Access where they have little or no experience, by using this approach my systems are far more robust that those which do not provide this level of configuration.

Disabled Living Foundation "We needed a unique database application; Dennis very quickly provided a user friendly, bespoke solution."  See my testimonials

I always supply my systems in a form where the end users cannot make changes to the design or programming code. I will supply your system as compiled mde files which operate faster that un-compiled mdb files and are not changeable by the end user, thereby increasing the robustness and security of your database system.

Opening Form
As I mentioned above I normally prompt users for a username and password when starting one of my systems, so this would be the first form they would see on entering the database system, for example:



If you did not have a requirement for different levels of access multiple users would see a form offering various options, here is an example where this particular user is not an adminstrator, so the administration options are disabled for this user.

Whereas, the overall administration user would have access to all the tasks, and the Administrator would be trained by me to be fully conversant with the operation of the entire system.



Mike Russell, Director, Air Heating Southern Ltd. said "After showing Dennis our current database he made some excellent suggestions on how he could improve it."  See my testimonials

Customised Menu System
I always provide a customised menu system in databases I develop, some Access Database Developers do not. This ensures that users can only undertake tasks as intended and not corrupt the database by wandering into areas they do not fully understand, as an example, here is a normal Access Menu

This enables the user access to many areas of the system where they could cause damage, probably unintentionally.

I supply databases with a customised menu system that only offers the facilities that are required, and is semi-intelligent to only offer facilities relevant to the current task.

Here you can see there are less options, and some are disabled (shown as grey) as they are not appropriate to the current task.

Here is another example where the available selections are different as appropriate for the current task

Forms
A system can have many forms; these are where you enter data into your system and are also used to interrogate your database to find information.

I always create forms in accordance with the clients requirements in respect of colour, font sizes etc. Also I always use real world names that my clients are comfortable with, for example 'Project name' 'Project Value' rather than database terms like 'Project Record'


Speeding up data input while maintaining accuracy
Speed of data entry can be greatly improved by a combination of programming and data structure. For example if you had a system that needed to record information about flights, I would present a box that the user could select a country from, titled Country; this would contain a list of countries sorted alphabetically, when the user selected a country (this is far superior to letting a user type a country as they may misspell it and that would have a detrimental effect on your data integrity), lets say they select 'United Kingdom' then I would program the system so that the next box titled 'Airport' would only contain a list of airports in the United Kingdom, thereby guiding the user rather than letting them enter data that could be inconsistent such as entering USA as the country and London Heathrow as the airport.

User Feedback
At various stages of operation it is useful to ask users to respond to questions or simply tell the user what is happening.

I always provide feedback using real world terminology, not all Access Database Programmers do this. For example if a user had selected to delete a customer I would display a message to the user such as:

Here you can see the title says 'Customer Deletion' so the user knows what the message is about, the message box shows a red X so that the user knows this is an important question, the message itself is clear about what will happen if the user clicks 'Yes' The default button has been set to 'No' so if the user accidentally presses the enter key the deletion will not take place.

As opposed to

This simply says 'Microsoft Office Access' in the title, there is no red X, the message simply says 'delete' and the default button has been left as 'Yes' so if the user accidentally pressed the enter key this customer data would be deleted.

The creation of systems that provide the first type of message to the user rather than the second type comes from experience and many other developers may not have the required level of experience to design a really user friendly interface.

As well as programming in the functionality described above I also create feedback messages that speak the users language, not a database developers language, so no words like Record, Database, Index, but words appropriate to your business, for example, Invoice, Customer Order, Client Request.

Clive Patten, Managing Director, ABS Wastewater Technology said "Dennis was able to exactly meet our design specification. The delivery was on time and the system worked faultlessly from day one."  See my testimonials

Reports
A report is where you produce outputs from your Access Database system.

Normally clients have requirements for at least a few basic reports, whereas some clients require very complex reports.

I normally create a form that acts as a menu to the various reports available.

When the user selects a report I display it on screen, or if you prefer I can send it directly to your printer, most users like to see their reports on screen first to ensure the data the report contains is what they were looking for.

Here is an example of a form requesting complex selection criteria from a system I created for Canon Consumer Imaging

Here the user can select multiple criteria in order to be presented with a report that exactly matches their needs. This also has an option to produce the report in one of three ways, to the screen, to a file, or directly to a printer.

When the report is run, it displays the user's selection criteria on the first page


The subsequent pages display the information matching those criteria

Some of the data has been removed from this report for display here.

Branding
I can brand your system with your company colours and logo as can be seen in the report examples above, and in the form below.

As mentioned above under 'Customised menu System' you will not see the standard Access Menus.

You will not see any programming code

You will not see any raw data tables

You will not see any data queries

The things mentioned above are for programmers to deal with, not end users.

I always endeavour to create systems that are easy to use, are not confusing and are fast.