Excel vs Access (1 Viewer)

[tmyers]

Going on a rant, so I apologize in advance.

I am sure others have run into this sort of thing over the years. How would you convince people who are so engrained in Excel because they have used it for probably 2 decades (since the workplace had no better options), to actually give Access a try?

In my particular case, I have an older gentleman in my department who has used Excel for probably 30 years (if its been around that long). Since he is also the most experienced, I have tried to keep the "flow" of the app in line with his process in Excel. Why mess with tried and true methods?

He is also my biggest opponent in trying to get my app fully launched. He tends to pushback on every minor detail and tends to rant over why I am even trying to create the app in the first place when there is nothing wrong with Excel. My typical counter to that is that no, our Excel sheet isn't perfect by any stretch. To make it work, we are constantly changing and altering formulas, moving things around etc. While the sheet is highly customizable, it is so prone to errors because you are always messing with things. All in all, it isn't user friendly.

When I first started in this department at the start of the year, I always heard complaining about how we had no central method for price tracking and such. How it would be wonderful to have such a system. In my off time, I started learning about Access and building a database to handle that. As time went on, I decided that if it was going to store all that info, why not also do quotes out of it?

Fast forward several months and I have a mostly (probably 90- 95%) functional app that stores all pricing info and can be used to do quotes from. I show it to the boss and coworkers and they are all excited to use it because it is almost everything they have wanted. The other coworker almost immediately shoots it down. Main reason? our Excel sheet ain't broke so why do anything different?

The app I spent months learning how to and building is unlikely to ever really see the light of day due to one stubborn person. I essentially said that at this point, if he wont even give it a look, why am I bothering? It has all the features requested (minus a small handful) and works. The only way we could do better is pay who knows how much money upfront + weekly/monthly cost and get a program like Oasis which they didn't want to at the start because of the cost of it.

End rant.

 

[theDBguy]

Hi. If this one person is the decision maker, then you definitely have an uphill battle ahead of you. Otherwise, maybe you can go around him, unless that's not a good idea based on your position or relationship in the company.

Just my 2 cents...

 

[Isaac]

Are you going out of your way to step in with the Excel worksheet, helping them avoid major mistakes that would otherwise occur?
If so, pull away from it a bit, allow a major failure to occur because of it.

Try to accumulate a list of issues that have arisen because of it and get it to a person high enough up who will tell Older Gentleman what to do.

Having said that, if you have a person who is below your boss, but who is so revered in the area that the boss is scared to tell him what to do, that's a very hard situation in the first place.

I also have some struggles, I have an area where Excel rules. Mostly because "we can sort and filter any way we want". I try to imagine what it would take to give them an Access interface that would just easily allow THAT MUCH sorting and filtering and re-filtering, and indeed, it seems a bit daunting at times.

 

[tmyers]

Are you going out of your way to step in with the Excel worksheet, helping them avoid major mistakes that would otherwise occur?
If so, pull away from it a bit, allow a major failure to occur because of it.

Try to accumulate a list of issues that have arisen because of it and get it to a person high enough up who will tell Older Gentleman what to do.

Having said that, if you have a person who is below your boss, but who is so revered in the area that the boss is scared to tell him what to do, that's a very hard situation in the first place.

I also have some struggles, I have an area where Excel rules. Mostly because "we can sort and filter any way we want". I try to imagine what it would take to give them an Access interface that would just easily allow THAT MUCH sorting and filtering and re-filtering, and indeed, it seems a bit daunting at times.

The sorting in Excel vs Access is one of his gripes. I have tried explaining that there isn't much I can do to remedy it because the sorting we tend to do in Excel doesn't make sense from a data stand point.

In what world does EM go to the bottom of the list by Z instead of in the E's? I cant quite rectify that.

It is a sort of situation where the boss wont really step on his toes because he is bar none of the most senior and knowledgeable person in the department. Everyone complains about the Excel sheet though because it is so prone to errors with formulas because of how much we have to modify it for every job.

Just the other day we had a 30k mistake because the formulas didn't adjust correctly after a line was inserted and no one caught it until later.

 

[plog]

I've learned that in a lot of places they don't fix small problems. Especially if they are problems that only cause work for other people. So you either need this to become a big problem, or a problem that the right people feel.

 

[tmyers]

I've learned that in a lot of places they don't fix small problems. Especially if they are problems that only cause work for other people. So you either need this to become a big problem, or a problem that the right people feel.

I can't really count how often mistakes happen due to formulas not updating correctly. Sometimes it is minor, other times it can be major. I would say in the last probably 4 months, we have had upwards of half a million dollars in issues spread across who knows how many jobs. Now we wont technically lose that money. It can be made up across a job and such, but that doesn't change the fact it should have never happened.

 

[theDBguy]

I also have some struggles, I have an area where Excel rules. Mostly because "we can sort and filter any way we want".

The sorting in Excel vs Access is one of his gripes.

Gentlemen. I am curious to see some examples of those sorting/filtering issues. I don't use Excel much, but if I understand it correctly, Excel can only filter using the AND operator; whereas, Access can use AND/OR.

In Access, you can either use a query (or give them a form to create their own query) or present the data in Datasheet view to take advantage of the built-in sorting and filtering dropdowns.

Thank you.

 

[Isaac]

present the data in Datasheet view to take advantage of the built-in sorting and filtering dropdowns.

That's great advice, that's just what I was fishing for. This is something I will keep in mind!

I was mostly just thinking of the filtering-upon-filtering-upon-filtering, where you filter one column, then another, then another, etc.
Obviously that can be done in Access, it just requires extra work and can seem a little daunting if you get the slightest thing wrong

 

[tmyers]

Gentlemen. I am curious to see some examples of those sorting/filtering issues. I don't use Excel much, but if I understand it correctly, Excel can only filter using the AND operator; whereas, Access can use AND/OR.

In Access, you can either use a query (or give them a form to create their own query) or present the data in Datasheet view to take advantage of the built-in sorting and filtering dropdowns.

Thank you.

In my environment, the sorting isn't technically sorting at all. It is "sorted" based on how the user input the values. So you can have nonsensical list like:
R1
R2
R3
R10
A1
A3
E1
E3
I can't find a way to allow such nonsense in Access. Access would sort that like:
A1
A3
E1
E3
R10
R1
R2
R3
Which irks said person lol.

 

[The_Doc_Man]

Document the errors to the bosses. Explain that it is a pay-me-now-or-pay-me-later situation. That older gentleman needs someone with clout to clout him just a tad. But the part that really makes me wonder is, if this is a shared worksheet and people are sorting it all over the place, how do you coordinate the sorts? Do you open the workbook in Exclusive Access mode and thus force people to wait their turn? How is that managed?

By the way, spreadsheets have been around for well over 30 years though I am not going to swear that Excel has been around that long. But there was a spreadsheet called EasyCalc that worked about like Excel. Less capacity because it was on DOS (!) machines which have limits on address space size. But a worthy predecessor.

 

[Steve R.]

Going on a rant, so I apologize in advance.

I am sure others have run into this sort of thing over the years. How would you convince people who are so engrained in Excel because they have used it for probably 2 decades (since the workplace had no better options), to actually give Access a try?

Excel and Access serve too vastly different purposes. Excel is a spread sheet for analyzing numbers. Access is a data management program. The "problem" is that Excel is easier to use and also offers data management features. So it is "natural" to start with Excel and then get "stuck" with it as the established method of working. Fundamentally, the solution would be to ask people what they intend to do with their project before they start and to then steer them towards either Excel or Access based on how they respond.

 

[Jon]

Many years ago, I had a rather hilarious situation! It was in the early days of Windows, and I was tasked with creating an accounting system for a property development business. The accountant was used to using a DOS spreadsheet package, perhaps lotus 1-2-3. He said he didn't like all this Windows rubbish. So, I had to create an Access database that looked like DOS! Courier font and black background!!

Oh those were the days!

 

[The_Doc_Man]

Determine how said irksome person WOULD sort it. Add a column to the table holding those elements. In that column, store a number to act as the sort key. Surely the order he wants is based on something rational, so allow for sorting on that.

Sometimes, though, the only way to handle "Irksome Old Gentlemen" is challenge them on their viewpoint by explaining what CAN'T be done in Excel - such as normalization as a way to NOT have to propagate changes to numerous rows.

 

[tmyers]

Document the errors to the bosses. Explain that it is a pay-me-now-or-pay-me-later situation. That older gentleman needs someone with clout to clout him just a tad. But the part that really makes me wonder is, if this is a shared worksheet and people are sorting it all over the place, how do you coordinate the sorts? Do you open the workbook in Exclusive Access mode and thus force people to wait their turn? How is that managed?

By the way, spreadsheets have been around for well over 30 years though I am not going to swear that Excel has been around that long. But there was a spreadsheet called EasyCalc that worked about like Excel. Less capacity because it was on DOS (!) machines which have limits on address space size. But a worthy predecessor.

I had a feeling Excel probably out dated me (I am only 30), but spreadsheets had been around awhile.

There isn't a "centralized" Excel form. We each have a template that we use to start a job and it gets modified from there. Every job is different and every persons sheet is different. It is a mess. The only constant across everyone's sheet is the look of the end result. That is where all similarities stop. People have added in things the like, others have removed things etc. So when one messes up, we tend to not catch it right away due to that.

 

[The_Doc_Man]

Jon, at least if you are using Courier font, you can predict the size of the displayed strings. That font is non-proportional. With Times New Roman and kerning, you can't predict SQUAT for displayed string length.

 

[tmyers]

Many years ago, I had a rather hilarious situation! It was in the early days of Windows, and I was tasked with creating an accounting system for a property development business. The accountant was used to using a DOS spreadsheet package, perhaps lotus 1-2-3. He said he didn't like all this Windows rubbish. So, I had to create an Access database that looked like DOS! Courier font and black background!!

Oh those were the days!

That sounds like a formatting nightmare.

 

[Jon]

I did what he said. I've never seen an Access database looks so retro since then!

 

[Isaac]

Many years ago, I had a rather hilarious situation! It was in the early days of Windows, and I was tasked with creating an accounting system for a property development business. The accountant was used to using a DOS spreadsheet package, perhaps lotus 1-2-3. He said he didn't like all this Windows rubbish. So, I had to create an Access database that looked like DOS! Courier font and black background!!

Oh those were the days!

Courier font ROCKS

 

[Jon]

I think retro is making a comeback!

 

[tmyers]

I am a simple man. I have all my stuff set to Arial.