Access can be more than just order processing
Amongst other uses it can be used for
Club Memberships
Bookings
Stock Control
Accomodation
Reservations
Student Classes
Product Ordering
If it is something that can be written down then it can be stored and retrieved as data, storing this data in a database can vastly improve the efficiency of everyday operations.
VBA
You may have heard of VBA or Visual Basic for Applications but what is it?
It is essentailly a programming languge dedicated to Microsoft Office applications and is an implementation of the common Visual Basic code. Using this coding language it is possible to extend greatly upon the existing database to perform operations that aid the user.
Automation
Perform laborious tasks with little effort by letting code do all the work such as mass emailing and printing and backup data
Validation
Input data within certain criteria, flag on fields that have been missed or inform you of duplicate values.
Default values
Insert default values such as the current date, city or county or just prefixes. It will all save time for you.
Calculations
Calculate stock levels, future payment dates and order totals.
Share Access
Although Access can just sit on its own to perform its database duties, it is also designed to be shared on a network. Access can easily be shared by splitting off the tables into a shared location that can be accessed by multiple users.
Did you know that just a single copy of Microsoft Access can be used to create a package solution to deploy a runtime version of
Access to multiple users. This runtime version is scaled down version of Access that can still allow a user to input data and run reports.
How to store your data
If you're having trouble choosing between Access and Excel, take a moment to answer an important question: Do you need to organize your data into a relational data structure or a flat data structure?
A relational data structure divides your information into logical pieces, and places each piece in a separate table.
For example, a sales database typically puts information about customers — their names, addresses, and other key facts — in one table, and information about what those customers buy in another table.
By placing alike data in the same tables they then can be joined up to data in other tables that they can relate to, such as customers and their orders.
A relational data structure has several advantages:
- It lets you answer important business questions, such as who bought the most (or least) of your product last week.
- What's more, using separate tables can make your data easier to manage, because each table holds just part of your information.
- And finally, a relational structure helps keep your information accurate, because you can prevent users from entering data in the wrong table.
In contrast to a relational data structure, a flat data structure is a simple table that isn't related to other data. For example, a grocery list is a flat file, and so is a list of your friends and relatives. Flat data structures are easy to create, and they're also easy to maintain, as long as you don't have too much information. Flat data works nicely in Excel. In fact, Excel is designed to create and maintain flat files.
In case you're wondering, each table in a relational structure is also a flat file. This relational structure ultilises the powerful SQL query language to pull data from your tables for just about any imaginable scenario whether it is for sales data, customer locations, what is the most popular product for a given age group or even select your top 5 customers to give them a discount.
So how can you tell if you need a relational structure? By answering a few more questions:
- First, do you have a lot of repeated data? For example, do you constantly enter the names of cities or counties? If so, you can use a SQL query to pull that repeated data from a table and automatically populate a form. Doing so can save you time and effort because you don't have to reenter the same information each time you create a new record.
- Second, do you want to track actions or events? For example, do you want to track sales or customers feedback? Any time you want to track an action, a relational data structure works best.
In summary if you need to store, manage and query large amounts of data then Access can support your needs. If you need to analyze that data then Excel can give you a plethora of analysing options.
Here we see relational tables in practice, the tables are joined together to show how they relate to each other. These joins allow for referential integrity which ensures that data can only be entered if it relates to data in another table. This vastly reduces the risk of errors when data is being inputted into a system.