Excel vs Access (1 Viewer)

[CJ_London]

many years ago I had a client who processed sales commissions for 600 sales people using Excel. It required a team of 5 people and there was typically around £2m/year overpayments which weren't recovered. There were 6000 queries a year from the sales people.

I introduced an Access solution, took six months to develop. Overpayments ceased or were recoverable the following month. The number of queries dropped to less than 100/year and we were able to reduce the team from 5 to 3. Other benefits include the sales people now trusted the system and spent less time looking for errors and more time selling.

 

[The_Doc_Man]

Ah, yes - but CJ, beware of doing good things. Murphy's Law has lesser rules including: No good deed goes unpunished.

One of my skills is optimization of programs. My boss of the time, perhaps 40 years ago, asked me to optimize a format conversion program we used for preparing a ROM "burner" to program our custom-configured telemetry controller chips. The program was written in a PDP-11/70 RSTE/E version of BASIC and we were switching to VAX 11/780. The RSTS/E version converted our burn file in about 22 1/2 minutes. The VAX version took 21 3/4 minutes even though the VAX was a MUCH higher-rated machine. To make the long story shorter, I fixed the problem it had with string garbage collection by doing string ops via an assembly language subroutine that didn't need garbage collection. The same input that took 21 3/4 minutes in "raw" BASIC now took 17 seconds. Everyone was suitably impressed. At least, I thought so, until Brad F., one of the programmers on that project, passed me in the hall two days and said, "Thanks a LOT you S.O.B."

Of course I was shocked and had to ask Brad what I had done to earn his displeasure. He said, "Used to be that when we started a burner conversion we had an automatic 20-minute coffee break. Now we don't even get a potty break. You screwed us all bigtime." All that was left for me to do was to apologize.


More to point, one of my projects was to take over an Access app that one of our Navy guys had written for the engineers so we could make filtered reports per project. The engineers had done their work with a huge spreadsheet with (as I recall) about 30 columns and this person had just linked to the spreadsheet as an external table. Things kept on crashing even when Access WASN'T in use because Excel's sharing rules are nowhere near the same as Access sharing rules. I was able to eventually migrate that table into a back-end table with Access and stop the crashes. I had forms to let them do editing on a record (and search for records). There were a FEW diehards but even they relented when they saw how stable the Access stuff was. And they could ask for me to implement reports or special computations for them, things that they weren't sure how to do for the Excel version of what they were using. Sometimes, the best way to convince someone of the value of something is to SHOW them.

 

[Mike Krailo]

One of the main things I really hate about Excel in the office where many persons have to access the same central file is that once one person opens up the file, others can only open it read only. That's what kills it for me about using Excel for storing all your data. With Excel in a typical office, there are zillions of versions of many different spread sheets. That's a nightmare to manage.

Contrast that with Access and everyone can access the same centralized data at virtually the same time. That's your selling point tmyers.

 

[Isaac]

One of the main things I really hate about Excel in the office where many persons have to access the same central file is that once one person opens up the file, others can only open it read only. That's what kills it for me about using Excel for storing all your data. With Excel in a typical office, there are zillions of versions of many different spread sheets. That's a nightmare to manage.

Contrast that with Access and everyone can access the same centralized data at virtually the same time. That's your selling point tmyers.

Be surprised how far Shared Workbooks functionality can be pushed. Before I started developing in Access, I proved they can be pushed quite a ways. Not recommending it, though. Mostly just being humorous here.

 

[Minty]

Money Talks.

At the end of the day if you can demonstrate the savings in missed errors, which seems apparent from your earlier posts then it should be no brainer.

I would speak to the boss, and ask to trial it with staff members other than your stuck in his ways man. Once they are confident it works and doesn't have any issues and is making them more productive with less errors, you will probably find that you can develop some killer feature that doesn't exist by using Excel, everyone will be a convert, and 'ol "Stick in the mud" will get left behind.

 

[tmyers]

Ah, yes - but CJ, beware of doing good things. Murphy's Law has lesser rules including: No good deed goes unpunished.

One of my skills is optimization of programs. My boss of the time, perhaps 40 years ago, asked me to optimize a format conversion program we used for preparing a ROM "burner" to program our custom-configured telemetry controller chips. The program was written in a PDP-11/70 RSTE/E version of BASIC and we were switching to VAX 11/780. The RSTS/E version converted our burn file in about 22 1/2 minutes. The VAX version took 21 3/4 minutes even though the VAX was a MUCH higher-rated machine. To make the long story shorter, I fixed the problem it had with string garbage collection by doing string ops via an assembly language subroutine that didn't need garbage collection. The same input that took 21 3/4 minutes in "raw" BASIC now took 17 seconds. Everyone was suitably impressed. At least, I thought so, until Brad F., one of the programmers on that project, passed me in the hall two days and said, "Thanks a LOT you S.O.B."

Of course I was shocked and had to ask Brad what I had done to earn his displeasure. He said, "Used to be that when we started a burner conversion we had an automatic 20-minute coffee break. Now we don't even get a potty break. You screwed us all bigtime." All that was left for me to do was to apologize.


More to point, one of my projects was to take over an Access app that one of our Navy guys had written for the engineers so we could make filtered reports per project. The engineers had done their work with a huge spreadsheet with (as I recall) about 30 columns and this person had just linked to the spreadsheet as an external table. Things kept on crashing even when Access WASN'T in use because Excel's sharing rules are nowhere near the same as Access sharing rules. I was able to eventually migrate that table into a back-end table with Access and stop the crashes. I had forms to let them do editing on a record (and search for records). There were a FEW diehards but even they relented when they saw how stable the Access stuff was. And they could ask for me to implement reports or special computations for them, things that they weren't sure how to do for the Excel version of what they were using. Sometimes, the best way to convince someone of the value of something is to SHOW them.

I have a friend who does programming and optimization and one of the things the bothers him every day is that if he does a stellar job on his end, it more than likely means someone on the other end loses theirs.

 

[Steve R.]

I have a friend who does programming and optimization and one of the things the bothers him every day is that if he does a stellar job on his end, it more than likely means someone on the other end loses theirs.

Were I worked eons years ago, we used to write environmental impact statements. They were approximately 1,000 pages in length. That required a small staff of typists and editors. Our office got a small Unix box. I was able to automate the assembly of this document directly from the professionals doing the typing/editing. No more typing pool or editors having to type and collate the individual "chapters" into a final document.

Of course there was an unintended consequence. Draft documents were sent to HQ for "approval". On one of those drafts, HQ was livid that their "requested" changes were not made. Turned-out that they had reviewed a prior version, an endemic problem with computers saving everything. The solution, added a date stamp at the bottom of each page.

 

[NauticalGent]

I have had this problem many times. One of my first real projects was how to move about 6 people from one centralized spreadsheet (on a share drive) to a viable (multi-user) solution.

Clients do no like to think in the "what if" mode. They need to see what you can do for them right up front. I did not know this and it took me a LONG time to get them to start using my app. Most of them loved it but there was one hold out and what was the most frustrating is that she was my main target and the one I worked most closely with!

I caught her working with the spreadsheet, which she updated manually from the info from my app, and she blushed and acted like a kid who just got caught looking at porn...

I sat down with her and asked her just WHAT that damn spreadsheet was giving her that the Access App was not....her answer: the ability to color code the records so she could quickly determine the overall status - a typical traffic light setup in other words.

In summary,
1. Do not let them see you made the sausage, have it was finished as possible when you show case it.
2. Find out what it is that makes them love their spreadsheet so much. Sometimes it is something ridiculously simple to create

Keep us posted on your progress please!

 

[SurreyNick]

It’s not uncommon for employees who have been in post for many, many years to possess excessive and unwarranted influence. This can become a problem if that person is older and just wants to ride out their time to retirement because they may have drifted into ‘coasting’ mode. These employees don’t like change and they don’t want to learn anything new, in fact they will often and unreasonably resist anything that isn’t nice and familiar. Rather than admit to it though, they will use phrases like “if it ain’t broke don’t fix it”, “we’ve always done it this way” etc. etc. So, what do you do?

Well, if an employee’s unwillingness to consider/adopt a new method of working is purely irrational and there are proven and measurable benefits to a new procedure/system then the answer is to find a way to move these 'naysayers' from a position of influence to impotence. Not by changing their job, but by isolating them and their opinion.

In your case I would suggest the way to do this (if at all possible) is to operate both methods concurrently. It will mean more work in the short term because you, or someone else, will need to double enter everything but the benefits of this approach are clear. You can run it real time and iron out any bugs. You can then demonstrate its advantages through its use and this in turn should generate a groundswell of opinion and support in favour of the new method. And finally, the boss can reassure himself/herself that if they adopt the new method they won’t end up with egg on their face. At this point you will have successfully isolated the naysayer and removed the weight of their opinion, empowering the boss and the other staff to say "like it or lump it". It goes without saying that it will help enormously if you have the boss's buy-in from the outset.

I have used this isolation technique many times in my working life with considerable success.

 

[tmyers]

I have made that into part of my pitch. The man is retiring in 4-5 years, so why base a decision off him? Obviously I still want his input as his experience in the process that is existing shouldn't be overlooked when developing a new app, but dang his negativity gets annoying.

 

[zeroaccess]

I have had this problem many times. One of my first real projects was how to move about 6 people from one centralized spreadsheet (on a share drive) to a viable (multi-user) solution.

Clients do no like to think in the "what if" mode. They need to see what you can do for them right up front. I did not know this and it took me a LONG time to get them to start using my app. Most of them loved it but there was one hold out and what was the most frustrating is that she was my main target and the one I worked most closely with!

I caught her working with the spreadsheet, which she updated manually from the info from my app, and she blushed and acted like a kid who just got caught looking at porn...

I sat down with her and asked her just WHAT that damn spreadsheet was giving her that the Access App was not....her answer: the ability to color code the records so she could quickly determine the overall status - a typical traffic light setup in other words.

In summary,
1. Do not let them see you made the sausage, have it was finished as possible when you show case it.
2. Find out what it is that makes them love their spreadsheet so much. Sometimes it is something ridiculously simple to create

Keep us posted on your progress please!

I agree with this and the above that people generally can't conceptualize "what ifs". Only an access developer will truly understand what they have in mind, so people need to see it working in its completed form to have their eureka moment. If you try to pitch this up front, it may sound nice but it's going to be ignored or even shot down from the start.

And, while ideally you'd have a working prototype, you may end up in hot water for spending work time on an unapproved project that you hadn't previously disclosed the hours for. I'd consider your business culture before proceeding.

 

[Isaac]

And, while ideally you'd have a working prototype, you may end up in hot water for spending work time on an unapproved project that you hadn't previously disclosed the hours for. I'd consider your business culture before proceeding

This is always my challenge. I've often been told, "ok, give me a mock up of how that would look and behave". I'm thinking well to give you a good mock up I might as well build it, that will take some real time.

 

[HillTJ]

tmyers, I share your frustration first hand. Some 18 months ago I joined a highly successful multi site privately owned manufacturing company. Being privately owned I cannot gauge the "turnover" but I'd guess in order of $200M Aus (to give you an idea of the size of the organisation). I bought with me a CRM program that I wrote in access (my previous Boss hated the program because every Friday afternoon I simply pressed one button which automatically emailed him with a report of that weeks activities & a schedule of activities for the following period, which could be a week, month etc. The other reps had to do their penance and spend most of each Friday stuffing about compiling similar reports in a sort of word/excel hybrid mess that took them all day to compile. Yes I did get a bit cocky and press the button a few minutes prior to the 5:00pm deadline each Friday, it added salt to the wounds, was fun to me. I resigned shortly thereafter).

Anyway, I change companies. My new boss sees the potential of this system & we add additional tables to develop a proper quoting system for him. I was never happy with the result, in my view it was never a finished product but he pushed me and has been happily using the program trouble free for at least 12 months on a single PC, not split database. Previously he was using a mess of spreadsheets.

At one point I split the DB, did the usual front end on local PC, back end on server installation & it all worked a treat. I then approached our IT department for assistance in converting my back end for SQLServer. My end goal was to be able to remotely access the DB for travelling Reps & not just use terminal services etc. I am not an IT person, so really needed some assistance as it was well beyond my pay grade. I could never get support at any level in the organisation, so dropped the whole thing. My Boss continued using it. Happy days for him.

Our other division (Head Office I may add) has similar needs and requirements (they sell similar products!). Their quoting system was an as primitive as it can get excel spreadsheet, with 140 tabs!!!. They are (still) manually copying a "Master" worksheet & pasting into a new tab in the workbook for every new customer that comes along! It gets better, the cell references & formulas are wrong. If there is a change in, say, a material cost, then the rep edits each of the existing 140 tabs and manually changes a cell to the new value!!!!.

I persist, I find another system that is in poorly implemented. It's to do with Plant safety records, you know hazard audits, workplace inspections, accidents/incidents etc. With some excellent help of forum members I've developed a thorough system, integrates with outlook email & calendar for scheduled events etc. Split & running on 3 PC's currently. Beautiful.

We get a new "divisional' boss, I'm asked to demonstrate "our" system. He's gobsmacked!, he apologizes to me and says that he's embarrassed that such a system does not already exist. He requests me install in all other branches, which is a great recognition for me & I'm chuffed. He requests I coordinate another "teams" meeting to demonstrate the system to the newly appointed "Divisional" Safety Manager. I do so. He's not "over the moon" like everyone else, says something like he needs a system that captures the "Costs" & financial aspects of Workers compensation etc. Something that was never on my radar, nor stuff that would have been available at my level anyway.

Result, project dead. I run it at my location, makes my life easier. No ones interested. All over. Company persists with a mess of excel spreadsheets! & no further progress.

Upon reflection, I've learnt a stack. My Access skills have developed a lot (good to be pushed outside your comfort zone). I've met some great Access Enthusiasts & mentors. Who's the winner?.ME.

Now, my Boss wants me to rewrite the original quoting database to capture some other aspects of quoting, not previously included. I'm not that keen, it's a lot of work & a lot of responsibility.

I "take my hat off" to professional programmers, it's pretty scary when an organisation is relying upon your product. Nothing I've been asked to do is really "Mission Critical". Those projects, I'd refuse & say are well beyond my level of experience.

I say, don't be disheartened. No matter what happens with your project, you've learnt skills that you take with you.

 

[The_Doc_Man]

@HillTJ - Here's a little hint: Small-to-medium offices such as you describe don't hire professional programmers to do in-house optimization. Such people, if they work for those companies at all, get hired to make new programming products. That's because managers have to deal with IT people who have the illusion that they own the corporate network. It takes a manager to remind them that if the corporate network doesn't serve the corporate interests then they won't have a job and someone else will take over who CAN serve corporate interests. Happens even with U.S. Government networks. (I was on a Navy network.)

I was a system admin for the Navy but because I have a varied background, I'm not a one-trick pony, as the USA saying goes. I have written device drivers that run in kernel mode. I have written O/S-like routines to do virtual demand-paging with least-recently-used retirement. I have written display routines. And I have done database work. So when the boss needed something to free him from a literal TON of Excel spreadsheets every week, I told him I could try to automate some of that if he wanted. Didn't matter to him that it was not actually on the books. As long as I kept up my assigned machines, any spare time could go to his project. He was thrilled when I got it working enough to give him reports that took at most about 10 seconds to generate. He loved it. But there were always the diehards who wanted stuff that couldn't be done quite so quickly and who didn't want to use my system because they couldn't make it cut/paste what THEY wanted to say.

Eventually the government wanted to look at my system to take it over and do something "on the books" to handle the functionality, but before they went that far, they found a relatively new 3rd party product to do most of what I was doing - though not all. Sometimes when you fire up a project to improve something, someone else will like the idea so much that they will move heaven, hell, and small sections of earth to take it over and make it their own so THEY can get the glory. If you are wage slave like I was, sometimes you learn to swallow your pride, take your boss's thanks for helping for at least a while, and go on to the next project. When the boss gives you your annual review, you at least have ammo for that slightly higher-than-normal pay raise. The larger the company, if you aren't a manager, the better it is for you to swallow your ego and take cash.

 

[AccessBlaster]

It's funny how people so different can experience the same thing. Like many others we used worksheets to solve our logistics issues.
We had formulas unprotected within the worksheets, people were constantly erasing the formulas.

The way I eventually convinced the powers to be to use Access was through increased security and multiple users. Having a copy of the data on the server basically sold itself.

The biggest problem was having a rookie designer with a vision and no experience.
Best of luck with your project

 

[HillTJ]

Guys, great stuff. It's encouraging (if that's the right description) to know that i'm not alone with this. And, yes Doc_man, my work went pretty much under the radar, until my boss sprukes to his supervisors about the "project" WE developed. To be fair he did the colouring in. Helps him heaps, but i can no longer do this stuff via stealth. I did get a bonus at xmass though. So nothing lost. As i said, i learnt stacks & am a better (amateur) developer as a result. I win.

AccessBlaster, yes i'm over it. They think i'm some kind of excel genius, just because i can use pivot tables & slicers. Senior managment thought i 'd attained some kind of advanced excel training. I said no, just an enquiring mind & YOUTUBE. Like this forum, the tools are out there if you make the effort!.

I'm thinking that i'll incorporate some kind of "expiry" on my application, nothing malicious but maybe a login required after say 12 months of use. Don't wish to be a prick but a bit of recognition would be good.

 

[The_Doc_Man]

@HillTJ - If you didn't SELL your app and didn't develop it ENTIRELY at home, don't put an expiration into it. I don't claim to know corporate law or intellectual property law in Australia. However, in the USA there is a very clear law that says if you develop something as a tool for someone while being paid for working on the topic that your software addresses, the person who pays your salary has at least a reasonably good claim to own what you wrote. A lot of what I wrote, even though technically off the books, fell under the USA Copyright Law clause about "work for hire" - so I cannot offer any of the modules as examples. It is no longer my code and when I retired, I had to destroy any/all home copies thereof.

 

[HillTJ]

Doc_Man, you are correct, i guess i was just "venting". Sorry, this forum is not for airing personal grievances. It's frustrating when you know that you can offer better solutions.....

 

[Isaac]

@HillTJ I like a lot of what you said. One of them is the idea of politely refusing to make an app you know is beyond your skillset or current.
That's a level of integrity and maturity that a lot of developers don't have.
Non-technical Management generally does NOT understand that the idea of "can-do attitude that says YES to everything" doesn't apply to technical design, any more than it would apply to Translation, Heart Surgery, or any other technically precise craft that you don't just go volunteer to do something beyond what is SAFE.

One other thing. While it's great to take companies from Excel to Access, I've also proved in my time that Excel can be taken far more than most people do. In this I am NOT referring to using it like a database with a lot of relational type stuff, just maximizing the capability of Shared workbooks, which is something , surprisingly, a lot of people don't even know about for some reason. Maybe it is less common in 365 or something.

 

[Isaac]

Sorry, this forum is not for airing personal grievances

I dunno about that - it's the Watercooler after all