Why struggle with MS Excel when MS Access can do a much better job!
Posted: Tuesday, July 21, 2009
by Michael Jillions
Mill House Data Solutions Ltd
MS Access is just a bigger and better version of Excel, it can do everything Excel can do but a lot more and yet I still see people with hoards of spreadsheets all with formulae cross referencing from another. Why are these people still using Excel for this job!
There are a lot of people out there that are using MS Excel to house their accounts or whatever with complicated formulae and "vlookups" acting as links between the different spreadsheets. Most of them are struggling to keep tabs on everything with the spreadsheets becoming more and more complex and each task more and more difficult.
So what exactly is MS Access? Well, it is package that enables the user to build bespoke database applications with large datasets, but, put simply, it is a more powerful version of MS Excel, with all sorts of enhanced benefits over its little brother.
Let's look at the components;
In Excel, you have a "Spreadsheet" made up of "Rows" and "Columns". In Access, you have a "Table" made up of "Fields" and "Records". Both are one and the same, except Access is a lot better. For example, in Access a table can hold well over 1,000,000 records, whereas in Excel, 65,000 is the limit (I know 2007 has increased this but still nowhere near that of Access).
Each spreadsheet in Excel is independent of each other, apart from when the owner has inserted formulae and "vlookups" as mentioned above. But this becomes incredibly unwieldy.
With Access you have what is called "Relationships". This is where you can determine how one table, aka spreadsheet, relates to another. How? I hear you ask.
Well let's look at a simple example. Say you have a table that holds all of your clients with one record or "row" for each client. That record will have a unique ID, let's call it the "Client ID".
Lets then say that you have numerous orders from each client. In Access you would create a new table called "Orders". Each order record would have its own unique "Order ID" but would also hold the Client ID so that you know which Client the Order came from.
So we have a table called "Clients" and a table called "Orders". The Order table is related to the Client table through a "Relationship" called a "One-To-Many". This means that you have one "client" to many "orders".
So now, with each order, you don't have to enter all the clients details, just the ID. You can also then set an option to prevent someone entering an order against a client that doesn't exist. With Excel, without complicated VBA programming, you can't do this and so data integrity goes out of the window. Not so in Access!
Then, you can use queries, which are really pretty simple to get custom set of data made up from these tables.
Let's say that you want to extract all the orders relating to one particular client. You simply open up a new query, add the two tables (the relationship will automatically be in place) choose which fields you want in your results, i.e. "Client Name" from the Client table and all the fields from the Orders table and then type in the Client Name in the criteria and hey presto, you have your custom set of data.
Entering data is far easier in Access. Rather than having lists and lists of records which you have to search through and then scroll along to the right field or column to add or change a piece of information, you can have a nice user friendly screen that lays the information out in a lovely professional way, with a search box where you can enter part of a name and up come the relevant records. This is called a "Form".
Reporting is another major benefit, you can have predefined reports that feed directly from tables or queries which can then be previewed or printed at any time with all the current data neatly laid out. You can have buttons on a form that will automatically run reports or queries or open other forms and before you know it you have built yourself a custom database application.
I know I have probably lost a few people here but I wanted to make a point that there really is little difference between Access and Excel except that if you have anything other than a simple spreadsheet, Access will do the job a lot better! Don't be scared of it, embrace it!
This Article has been viewed 1,659 times. (Not updated in real-time.)
Top-level comments on this article: (3 total)Great explanation.
Great in theory, as long as Access works fine. I case of a bug however Excel is much easier to fix than Access. I currently have this problem - an Access database created by my predecessor which for some unknown ceased to function correctly. As I can't find the bug I will have to create a new database all over again, a huge waste of time.
Access is a relational database. Excel is a spreadsheet. A spreadsheet is a specialized database, or an application made by using a database. Excel is a much better tool for what it was designed to do, and is well worth the time learning how to use it. I think in most cases where calculations of many sets of numbers are involved, Excel is the tool of choice. If you collect data, let's say it's baseball cards, and everything about each card, then Access is the right tool.
Relational database management is just out of reach for most people on a practical basis, because of the SQL language. Not unreasonably difficult, but difficult enough. If spread sheeting is what you are after, then stick with Excel.
We want your comments! If you can read this, you don't have javascript enabled, so you can't use this comment system. Please enable javascript.