ICT Dept. Web pages

Excel Vs Access For Year 10 Projects!

MS Access is a database MS Excel is a spreadsheet. They are different types of software for different purposes. The ability to search data by means of a query is built in to microsoft Access. It is sophisticated and very versatile. In access a query can be saved and reused. Excel has simple search facilities which will enable you to search for exact matches and it will allow you to search based on criteria (for example salary > £8000 is possible), but most users are unaware of this capability.

It is in its ability to create a user interface that Access excels! Access has the ability to display only certain parts of the data through forms. Using forms it is possible to deny access to the data in the tables, whereas Excel has to display all of what is in it. It is possible to make forms in Excel but the forms show all the data in the table Getting Excel to show only certain types of data requires not inconsiderable of programming ability by using Visual Basic for Applications. Most people lack the skill to do this. It is much quicker and requires substantially less skill to complete this task in Access.

Excel	Access
Large predefined tables with a set size.	In Access there are no predefined tables. All tables are purpose designed for a specific task.
All cells can take all types of data	Within a table there are fields (Roughly equivalent to the columns in Excel) A field has a closely defined data format and only data of the correct format can be entered.
Rows can be numbered by pulling down a value the tables rows all have predefined numbers but these numbers are not part of the data.	Within a table there are records. (Roughly equivalent to the rows in Excel) Access has automatic numbering of records.
No equivalent to the primary key. Searches of large amounts of data is slow compared to a well designed database	When a field with predefined numbers is created this is usually set as the primary key. This field makes the searching of large amounts of data very efficient.
A table of data can be filtered. This means that one of the columns can be display only those rows that match the value selected. You can only select values that exist. If the cells in that column contain several words you can only match the whole you cant look for one word or part of a word	The table can be searched by a query. A simple query can search for a given value in the field. If there are several words in a data entry you can search for one word or part of a word, relatively easily.
Data can be searched in the same way old fashioned (i.e. mid 80s) databases were. Few users are aware of this feature. It requires you to make 2 or three sets of headings but once set up it allows you to search with criteria in more than one column of the table you have created. It is quite tricky to set this feature up and modern users find it hard to use.	You can easily use access to search with criteria related to several fields in you database it was designed that way and it is easy to use. There are wizards that guide you through the creation of complicated criteria - Excel doesn't have the complicated criteria and has no wizard type help.
It is possible to protect your data in excel so that the user cannot overwrite it. However it is much harder to prevent them from seeing it.	Data can be protected from overwriting and it can be partially or wholly hidden from the end user. There are several different ways of doing this depending on what you need to do.
Easy to set up and use	Harder to set up but once it is setup gives you a great deal more control of what the user can do.
Validation is possible in Excel. It has to be set up on individual cells. Validation was not originally a feature of Excel so early versions don't have this. There are no input masks.	Has sophisticated validation rules and all data must be of the correct type for the field before you even start on the rules. Has a sophisticated set of input masks.
Can create a good range of graphs and charts from data.	Uses Excels ability to make graphs can't do it if its the only package on the PC.
Has a range of functions to allow calculations to be performed.	Doesn't have this feature.

Making a comparison of this sort is not a very clever thing to do! These two packages are designed for different purposes. If you were asked to compare a race-house with a bicycle as means of transport you would end up saying all sorts of things like "has four legs" / "has no legs" . The ignorant user would then think the horse is superior. It only makes sense to make such a comparison if you put it into a context - which would you use to get to work in a city? Then a decision is possible and factors like "needs a stable" become relevant.