Development Solutions – Need a professionally developed database or spreadsheet solution? Our Visual Basic programmers are experts at producing professional Microsoft Access Databases, Microsoft Excel Spreadsheets, automated (macro driven) Microsoft Word templates.
Co-developing
We have a great track record at running development projects in partnership with our clients, where our experts mentor, assist and help to up-skill client teams, “hand-holding” through the development lifecycle.
Feedback on our Development & Consultancy Projects:
Thanks for your time and assistance…thank you very much again.
Bespoke Catalogue Database, Museums Division, Scottish local council
Thank you for all your work over the last number of months on this piece of work
Statutory documentation standardisation, formatting, proof-reading & copy editing project for UK Transportation firm
Reaction has been extremely encouraging and everyone on the team is very keen to start using this as quickly as possible. Thanks again for all your patience and forbearance – it’s been fun working with you and I think the outcome is going to be a great step forward for our business.
Profiling Tool, leading UK Pub Leasing Business
This is fantastic – thanks so much!
Spreadsheet Solution, Edinburgh based Investment Managers
It looks great, and ready to go…thanks to all for bringing this to completion on time.
HR Analytical System, Scottish headquarted Beer & Cider company
For information about our software application development services, contact us for more information
Costs depend on specific requirements, and are usually charged on a day rate basis. If this is of interest please call or email to discuss, or arrange a free of charge exploratory meeting.
- by phone: 01436 65304
- by email: [email protected]
Database Design Concepts
A database is a collection of information organised and presented in a structured way. There are many popular software applications which can be used to build databases from the humble spreadsheet to proper relational database management systems.
| Data = | Pieces of information. |
| Database = | Information Source. |
Reasons for using a database are:
| · Storing Data | Computerised data takes up less space than its paper equivalent. |
| · Finding Data | Locating data by computer is very fast. |
| · Sorting Data | Sorting and resorting data indifferent ways is achieved quickly and with little fuss. |
| · Updating Data | As it is stored centrally, data need only be dealt with once. |
Your Data will be organised, easy to maintain, easy to store and easy to retrieve
Types of Database Structures:
A Flat File Database – A simple table, consisting of categories (or fields) in columnar format and records entered in rows. Databases created in spreadsheet applications (e.g. Microsoft Excel) are flat file databases. An everyday example of a flat file or two-dimensional database is a telephone directory.
Relational – A relational database involves several tables. Data records can be extracted from several tables at the same time. Because these more advanced databases can involve multiple data tables they are sometimes referred to as three-dimensional databases.
Microsoft Access is a Relational Database Management System (RDBMS).
Flat file Database Vs Relational Database
Where a Flat File system deals with all the information on a single table a Relational Database System splits a broad range of information into several tables. This means that data entry is quicker with less wasted effort. Also ‘queries’ or methods of searching for specific data are much faster. The power of the relational database system lies in its ability to relate two or more tables together to retrieve and collate information.
- Because of data duplication for each record entered in a Flat File System.
- There is much more wasted space.
- There is more time spent on data entry.
- Queries will run slightly quicker in a Flat File Database System.
Database Layout
In a Microsoft Access database, data is stored in the form of a table. These consist of fields and records:
| Field | Field | Field | |
| First Name | Last Name | Date of Birth | |
| Record | John | Doe | 03 Nov 58 |
| Record | Patrick | Deer | 12 Jan 66 |
| Record | Anthony | Elk | 31 Jul 55 |
| A Field is a piece of data of a specific type: | e.g. First name or last name. |
| A Record is a collection of fields: | e.g. information about a person. |
| A Database is a collection of records: | e.g. your business card file. |
The power of the relational database lies in its ability to relate two or more tables together to retrieve and collate information.
Database Design
In a relational database, such as those created using Microsoft Access, information is stored about different subjects in separate tables. To bring the information together in a meaningful way, Microsoft Access needs to be instructed about how the different subjects (tables) relate to each other.
Steps in Database Design
The Purpose of the Database and what the data is going to be used for.
- Decide what information is being stored in the database.
- Determine the tables.
- Divide the information into separate subjects, such as Customers or Products. Each subject will be a table in the database.
- Determine the fields.
- Decide what information you want to store in each table. Each field is displayed as a column in the table. For example, one field in an Employees table could be First Name, another could be Start Date.
- Decide on the relationships between information.
- Look at each table and decide how the data in each table is related to the data in other tables in the database. Add the necessary fields to tables, or create new tables, if necessary.
- Determine the relationships between tables.
- Which field is the link.
- Refine the design. This comes after the database has been created, and the design errors have been analysed. This is done by creating the tables, entering a few records of sample data, and looking at the results, making any required adjustments, during the process.
- On the completion of steps1 to 5, the database can be created.
Spreadsheets or Databases?
When should you use Excel Spreadsheets and Access Databases for different types of data?
Microsoft Access is a database application.
Microsoft Excel is a spreadsheet application.
They possess many similarities, which can make it difficult to decide which program you should use. For example, both programs can store large amounts of data, run powerful queries and analysis tools to slice and dice that data, and perform sophisticated calculations that return the data that you need. Excel gains added data manipulation functionality through Power Query and PowerPivot.
Each application has distinct attributes providing their own advantages, depending on the type of data that you are managing and what you want to do with that data. For example, if it is your goal to maintain data integrity in a format that can be accessed by multiple users, Access is your best choice, whereas Excel is better suited for complex numerical data that you want to analyse in depth.
Excel Spreadsheets and Access Databases
In very general terms, Microsoft Access may be the best choice when you have to track and record data regularly, and then display, export, or print subsets of that data. Access forms provide a more convenient interface than an Excel worksheet for working with your data. You can use Access to automate frequently performed actions, and Access reports let you summarise data in printed or electronic form. Access provides more structure for your data; for example, you can control what types of data can be entered, what values can be entered, and you can specify how data in one table is related to data in other tables. This structure helps you ensure that only the correct types of data are entered.
Access stores data in tables that look much the same as worksheets — but Access tables are designed for complex querying in relation to data stored in other tables.
As a spreadsheet application, Microsoft Excel can store large amounts of data in workbooks that contain one or more worksheets. However, instead of serving as a database management system, such as Access, Excel is optimised for data analysis and calculation. You can use Excel to build models for analysing data, write simple and complex formulas to perform calculation on that data, pivot the data any way that you want, and present data in a variety of professional looking charts.
As sophisticated as spreadsheets have become, they still have some serious drawbacks. Spreadsheets are not ideal for long-term data storage. They only offer simple query options, do not guard data integrity, and offer little to no protection from data corruption.