Access vs. Excel 1

[PMSunshine77]

I'm trying to explain to my co-worker the differences between Access & Excel. She has a habit of saying, "Everything you're telling me you can do in Access, I can do in Excel." Personally, I think she's 1)lazy and doesn't want to learn a new program, and 2)a typical "Baltimoron" (yay for Baltimore, MD! How'd I grow up here and turn out ok?), not even trying to understand what I'm saying.

Can someone help me point out the differences -- in elementary or middle school language?

Thanks so much!

~ Liz

 

[RamziSaab]

It a database...made to retrieve information and input info

Example is the best way, i have a data for 5 consecutive years 1995-2000 lets say, there are aound 100 countries and each country has 100 different products. I only ever want to look at 10 or 15 countries for 10 products on whatever year or years. Using a query i can pull out the data in a fraction of the time that it would take me to do it on excel...

you can probably do everything in excel that u do in access but a) it waste time
b) if your data is growing it will be so hard to handle on excel
c) excel = spredsheet access =database a dictionary might be useful investment here

good luck

 

[AlanKW]

Two significant advantages are:
you can easily design user-friendly front-ends for end-users
Access is designed for multiple users. Excel is not. There are things that you can do in Excel for multiple yusers, but its not the same.

BUT - When I need to do quick comparisons, checking for dupluicates, I copy the table to XL and make an IF statement or 2 and get what I need faster than I could in Access. But thats just reference and auditting...

HTH

-alan

 

[MichaelRed]

PMSunshine77

I can and do use both - but I ONLY use Excel for those 'special' people who are unable and / opr unwilling to to be serious about their carrears. Then, I generally 'let' them create what ever 'information' they want in whatever format they desire and just import it into Ms. A.

Some specific advantages of Ms. A. are the capability to Verify and validate date entry and 'prompt' the user for invalid entries.

The capability to create queries with multikeyed relationships between parts of the data thus avoiding duplication of information.

Creation of multipart forms for both data entry and retrievial where seperate data elements are in seperate tables (with matching Keys/indicies.

Create groups in reports with -perhaps- nested group subtotals, running totals and grand totals, including page breaks at various levels.

Geenrating a series of reports from different perspectives and -possibly- different sources and have hte user be able to easily (GUI/Form) select the reports and their 'parameters' conviently.

Control access to the data and automatically record who entered what and when.

And MUCH more.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[accessqueen]

I know the feeling oh so well. I have had this dilemma several times with co-workers. My response is as follows:

1. The data will soon or later become to large for excel to handle.

2. Excel will not allow mutiple ways of viewing data and manipulating data as flexibily as Access will.

3. Excel does not allow for the Security features that are availble in Access.

To put it in a nutshell when it comes to Access I only have two things to compare and that is.

With Access ACCURATE DATA GUARANTEED
With Access SECURITY OF DATA GUARANTEED

;-)

 

[Clandon]

I can only support and back up what accessqueen states 100%. I work with very large files of many types, and excel has always dissappointed me with the limited amount of rows is can handle.

Great statements AccessQueen!

Cheryl

 

[dragonsel]

LMAO!!!

This is a great post - the Government Department (need I say more) I work for insist on using Excel as a database so I can deeply empathise!

I constantly bring up the foolish notion that Excel is for calculations (hence in similar office suites it is called Calc etc) and Access is for databases (i.e. storing and retrieving DATA).

I wish you lots of luck explaining,
Selene

 

[zdas04]

When I used to teach database theory (in a previous career) I would describe Access as a "source of data" and Excel as a "use of data".

Sources of data provide a data repository and a method of data orginization as separate entities.

Uses of data take a particular orginization of data and make it available for further manipulation without affecting the source data.

At some point in every project that messes up this distinction there are tasks that are simply too difficult to be accomplished and either don't get done, or require excessive resources to accomplish.

David

 

[jhall01]

ACCESS vs EXCEL:

1. Access cycles through data faster and can find complex criteria quickly.
2. Multiple user friendly unlike Excel
3. Redundant data runs rampant in Excel.
4. Data is accurate and easy to update in Access.
5. Excel is great for reports and complex calculations but not storing data.


Alot of people say this and get away with using excel. They don't like to switch cause no one likes change. They also don't like learning new things, because learning new things means acquiring new job responsibilities and most people enjoy cramming 3 hours of work in 8 hour days!!!!!

The word "Database" is also very intimidating to people. I try to get people to use access over excel here at work also. It is a slow and tedious process, but I am making progress...baby steps at most

 

[Garridon]

Also, the more data in Excel you have, the more likely you are to lose track of it. Image that you are using the spreadsheet to track open and closed suspenses. You are always going to have more closed ones than open--and at an ever increasing rate (given that all open ones eventually become closed ones). That means you have to sort through all the closed ones to find the open ones (and thereby, may miss deadlines because you don't see them on the spreadsheet). The list becomes very overwhelming very quickly.

In Access, you can show only the current ones, flag the overdue, flag the coming due, prevent accidental deletions, sort the closed by the last six months--in essence, you don't have to display all the data to find one piece of it.


Linda Adams

http://www.hackman-adams.com

http://www.david-hedison.com

 

[markphsd]

From now on it's not comparing apples to oranges, I now will say Apples Vs. Oranges.


Here's the thing, if she can do everything in excel that you can do in Access, then you aren't doing Access justice. Well, that or she doesn't know what you are doing.

Enough said.


Mark P.

Bleh

 

[ETID]

Properly setup,...Access prevents users from entering redundant data, slaps their wrists if they try to put data where it doesn't belong and yells at them if they miss key data that would render the record vitually useless.

It can even record the user id of the person editing records. (so you know who messed up the data or not).

To mirror what was said earlier....
With Excel, the power is in the reporting of data,...Access, the recording of data.

 

[rdodge]

I can also add to this:

Access does not allow for data corruption so easily by a user, such as selecting all but one column, access will still include all fields for sortings, Excel won't. Excel now has a warning dialog box that comes up, but in it's early days, it just sorted regardless. Therefore to say the least, Excel is very clumsy with data management.

Access can store X number of recrods, Excel can only store up to 65536 records on the same sheet without having to use a different set of columns or continue on with other worksheets.

Access has a 2GB RAM usage limitation as opposed to Excel having a 80 MB RAM (160 in XP and 03) usage limitation before it crashes.

Access can query data very easily and not run into issues too easily. In Excel, if using multiple files to compensate for the RAM usage limitation not to mention that calculation also eats away the RAM memory, try using External Link formula. This works fine at first, but when it references at least roughly 13,000 cells externally, Excel crashes.

Access Forms has lots of form events that one can use (though I don't care for how data validation works on bound forms/objects cause it not so user friendly for those that's predominantly mouse users, so I had to build with UNBOUND objects to make the forms more user friendly without sacrificing the strict data validation checks), Excel UserForms has very little Events to work off from.

Access has a fairly good security model, but I'm still not saying it's real good as it still lacks a few things for what I would like to see from a security stand point of view. Excel Security is nearly non-existent. Yeah, it does have security items, but it's not that great as I can demostrate too many issues such as users can still copy hidden cells on a protected worksheet and paste the information onto another worksheet that's not protected. That's just one issue of several.

Okay, so Access has all of these different advantages over Excel, so why then don't more users switch to Access?

Access is not nearly as intuitive as Excel is. This is where most users frenge away from Access.

Okay, so we still have a relatively large number of users that understands that this is going to require some additional learning and accepts that. Now as they get to building their DB program using the wizards and other automation features of Access, they then start to run into different types of issues as they test things out. At this point, frustration starts settling in cause things aren't working as well as one expected. Now, a large amount of the remaining people drops out at this point

Okay, so this means we have to do some Macro/VBA work, which in turn means we should learn how to write in SQL (Using the QBE helps a lot in the aid of this learning process) in addition to learning how to create macros/VBA codes, since that is what some of the RowSources are going to be dependent on within the forms and/or controls. At this point, we are basically down to the technical and programmer type of people to set this up.

After going so much further and learning how to work in the programming environment of Access, this group starts to run into other issues that is not liked. At this point, there's 3 options:

Option A, quit developing in the program and find some other program to do the development in. (Result, developer frustrated and doesn't want to continue)

Option B, continue programming in the program, but don't provide any solution to the issue (Result, developer gives up on trying to find a solution to the issue directly, but rather work around the issue, thus the impacted users gets frustrated)

Option C, continue programming in the program, and do some R&D to find a direct resolution. However, when the solution is found, it may require a lot more development time than what was initially planned for to still keep the requirements of the program, but at the same time, allow the ease of user friendliness (Result, developer may be frustrated and not like the issue at hand, but decides to continue programming including programming a work around into the program that will keep the requirements and allow for user friendliness of the program). Example of this that I had, users that predominantly use the mouse, if validation on a previous control fails before the focus is sent to the command button, the command button never gets triggered. This may be an acceptable behavior MAJORITY of the time, but THERE ARE THOSE FEW TIMES when it's not acceptable. This particular issue took me 2 months to find and create a resolution to this issue.

So by the time that all of these different levels of frustrations and issue went through, all you have left is a small handful of developers that reached the last option choice and went with Option C.



Okay then, how best to use Access and Excel?

Access should be used for all of it's DB means, and maybe even a lot of the analytical stuff via VBA coding.

Excel is good for 2 things

If you need to create a prototype, you can do this very quickly in Excel unlike in Access, it may take a while to do. However, don't spend much time creating this prototype as it's only meant to give a feel for how it would work on a general level basis in Access.

Excel is very easy to create things like charts and other data analytical tools from the data that may be retrieved from Access.

Excel is good for SHORT-TERM solutions while Access is good for LONG-TERM solutions.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[lespaul]

Nice analysis Ron, have a star!!

Leslie