There’s no need to wait for it…in the post you quoted, the sentence after the one you quoted was “it could also be a simple list of parts.”
Evidently, you’ve led a sheltered life.
I was just trying to acknowledge the odiousness of the original statement. How do you think I should have done that? Or maybe it doesn’t even bear mentioning.
Hmmm…interesting metric. What exactly is this ratio between? 1000:1 what?
I’ve worked for a company that managed its BOMS this way, and a more sophisticated container would have helped a lot. Lookups between different worksheets went stale all the time. Formulas broke a lot too; it was easy to brush the keyboard with a cell highlighted, erasing the formula in that cell. You can protect formulas, of course, but no one knew to do that in this company…many of them printed and filed every single non-spam email they got. Yet the system “worked fine” as long as you had been there for 30 years.
New employees got to parse a Byzantine and utterly undocumented set of spreadsheets scattered throughout the company. Looking for a part called a diaphragm? Well, you’ll only find about half of the parts with that name. One guy insists on spelling it “diaphram,” so a simple text search for the right spelling won’t find much. And so on.
If anyone needs me, I’ll be rocking in the corner.
Many years ago I ran across an engineer who used Lotus 123 as a schedule tool. They had a process that used blocks of material in sequence, and a rate at which they could use them - so either a block of material was used in 1 month, or part in one month or part in the next (or even over several months). His solution to the latter problems was to split the block in 2, duplicate the line with half the quantity in each line, and then retry - all automated with macro’s. Rinse and repeat until it was within error… He tried this, using a PC on floppies, only to find that after running for a while, it did not fit on the floppy. So he took the starter over to someone who had a 1.2M floppies and a hard disk PC-AT; but then he could fit the result back on the floppies. When I tried to explain to him how to program the calculation of the “leftover” for the month and then split to only that number (IF this OR IF that OR…), one split per month-end, his reaction was “that’s too complicated!”
Sometimes the problem is the tool on the other side of the keyboard.
What I meant earlier was that an Access database is a file. It allows multiple users to access it, but unlike a “real” database, it is not a separate process running outside the program actually running on the client machine. (Unless it’s changed significantly in the decade since I did any serious work with it). SQL is great, but any SQL server can be as complex as details like macro/VBA programming in Excel. There’s a range of application, between personal spreadsheet and full-on SQL server (or MySQL or Maria - how do you handle a problem like Maria? ♫ ) A SQL database should be for bigger, more complex problems.
The word database designers use is “normalization” - get rid of duplicate data. If you have a spreadsheet of say, employees, that lists states, sites, departments, titles - many of those will repeat many times. There is always a risk of typos introducing errors - “oops, we didn’t list this guy in the report because he was in New Yrok, not New York”. Normalization means making a separate table of valid values - but in a spreadsheet that means making a concurrent set of procedures for updating the tables; and how many of them do you need? Or VBA for verifying a data input is within the table and a set of drop-downs to select the value, etc. etc.
The question is - how relevant is the application, how important is it to get things right, and above all - how efficient? Business lore I full of legends of the guy who spent weeks fine-tuning a spreadsheet to save himself 5 minutes a day… and they processes change and he must completely re-do this. Again.
it’s sort of like the business operating decision of “do it however you like” vs. “we have strict procedures for that”. A procedure for how to authorize payments is far more important than a procedure for employees not to post cartoons on the cubicle wall.
For all of the tasks requiring some sort of data storage, 1000 should use Excel and 1 should have an application built or bought.
For example, for the systems that I manage, we put the list of servers and applications running on those servers in Excel. Sure I could have put it into some type of database and written a quick front end, but why would I spend the time and resources to do that when Excel works just fine for the need.
There are tons of those types of needs throughout organizations, typically smaller/local data sets.
“Components in production”. Not the same as parts. Promise.
One of my projects involved taking out, shaking down, cleaning up and loading back the whole QM Master Data structure for a large multinational which had previously created all their specs and inspection plans “freehand”. They had just treated SAP as if it was Excel, writing everything directly. A specific department manager was amazed that I could tell who had created each of their hundreds of inspection plans without looking at the “created by” column.
There is a measuring tool which in Spanish is called pie de rey. Each of the twelve technicians in his department wrote it a different way, and they used it in every single inspection :smack:
I see that while “components” overlaps with “parts” in popular usage, it may not overlap in this context. But still, I meant “components” in the popular sense. Sorry if that was unclear.
Oh, that’s painful! Serious question: I don’t speak Spanish, but I looked up that term and we’d call that tool “calipers” in English. An online Spanish dictionary suggests that “calibre” is an alternate to “pie de rey.” Is “calibre” obscure or otherwise uncommon?
The company I mentioned earlier had an SAP-like system but had no idea how to use it. One of the aforementioned spreadsheets could have been read into the system automatically or it could have been written out as a comma-delimited file and then read in manually. But this guy knew how to key numbers in, so by god, that’s what he was going to do. He would literally have someone email the spreadsheet to him so that he could print it out. He’d put the printed copy on his desk and manually key in the items in the printed spreadsheet. It was bananas. And yes, this guy is one of those who printed out every email he got and put it in a filing cabinet.
Good times!
Calibre has several meanings; as tools go, there are several by that name. The pie de rey is a specific one (a rule with a fixed stop and a mobile one, that you can use to measure the distance between the two stops), but calibre also refers:
- to the caliber of ammo,
- to the diameter of anything that’s spherical or cilindrical such as fruit or rods,
- to a flat piece (wood, plastic, metal) with sized holes cut in it (usually but not necessarily round ones), where you insert items in order to verify that they are in the desired size,
- and to several other objects used to measure standardized sizes, less common nowadays than either the rule or the piece with holes.
So, while referring to a pie de rey as a calibre is correct, it can also be ambiguous. In that particular company they used both the ruler and the pieces with holes, so they reserved calibre for the pieces with holes.
Ok; thanks for explaining. That sure sounds like an arbitrary heuristic. Is it? If not, what’s it based on?
Even if we both agreed that this was an appropriate ratio of spreadsheets to databases for a given organization, it only applies generally. It says that databases should be rare and spreadsheets should be common, yes, but it says nothing about whether any particular data set should be handled via a database or a spreadsheet. I think we know enough about this application to say it probably should be a database, but obviously others disagree with me.
I’m not saying that there’s never a reason to use a spreadsheet. I’m saying that (a) spreadsheets-as-databases tend to grow until they’re unmanageable, and (b) the OP has provided enough information (for me at least) to conclude that this is one of those spreadsheets. I’ve asked the OP for clarification, but obviously he hasn’t yet responded.
Of course sometimes it’s fine to put data in spreadsheets. But I don’t think it’s a stretch to say that the OP is entrenched and recalcitrant regarding this switch from Excel to Access. I’ve been on the other side many times. In my experience, a cry of “But our spreadsheet works fine!” doesn’t mean “let’s have a rational discussion about the needs of various stakeholders, those of the organization as a whole and how we can meet those needs with the available resources,” but rather “I fear change.”
Of course, my personal experience may or may not apply to this situation.
That makes perfect sense. Thank you for explaining!
Doesn’t Excel cost money, while Postgres (for instance) is free? I can imagine some bean counter finding that enough of a reason not to use Excel.
Bean counters are notoriously conservative as a group. I’ve had two clients which used OpenOffice (or equivalent) instead of MSOffice, but both were engineering firms at heart (one civil, one mining). In both cases the change was triggered by the ribbons; both are even happier to be in OO now that so many charging programs are on subscription models or on must-be-connected models. Both paid a contribution to the appropriate project based on how much use they were getting; a different company (in this case, chemical) did the same for IrfanView.
Nava’s points are solid. Also, bean counters are necessarily quite familiar with Excel, but few of them are likely to have any idea what SQL is. Implicit bias is a thing, so I suspect that few bean counters would dump a familiar program that’s critical to their work in favor of a free program that isn’t.
Also, OpenOffice and Libre Office are more than good enough for home use and probably plenty good for most corporate use.
I’d say that actually, one of the key benefits of moving data out of Excel into some sort of real DB is a factor you touched on in the post above - data in rows.
Excel allows you to sort or rearrange the data in a column independently of the data in adjacent columns. Most tabular databases do not - a row/record is an entity that stays together as a row. I’ve lost count of the number of cases I have seen (and been asked to support) where tabular data got jumbled up this way.
About Access though (and I am quite a fan, despite what I am about to write):
Microsoft Access is like those ‘entry level’ own-brand power tools you get at big-box stores; in appearance and feature set, they look a lot like the tools that professional tradesemen use.
But you shouldn’t turn up with them at a construction site because a) everyone will laugh at you and b) you will most likely perform a bit of a shoddy job, which will become someone else’s nightmare to put right.
Access is a great DIY-level tool; great for ad-hoc crunching or quick and dirty transformation of textual, tabular data and one-off reporting, but anyone who uses it to create user-facing programs or systems for a business is most likely creating a future support nightmare.
Yes to all of this! I like the house-brand big-box tool metaphor. This says most of what I was trying to say about Access except more succinctly and with less vitriol. 
Yes it’s an arbitrary ballpark-ish count of the number of things most organizations do with spreadsheets that are ok being in a spreadsheet (the 1,000) vs requiring an app (the 1). It’s based on experience from a long career of designing, building, buying and implementing enterprise software.
Here’s anther good example:
During a recent project for a distribution center, we reviewed the current process for handling customer’s routing rules for outbound shipments. The business has them in a spreadsheet which is a perfect place for them. It’s a small number of people accessing the data, the rules are inconsistent and map well to an ad-hoc approach for entering the data (Excel is flexible that way). I told them to leave them in the spreadsheet, there was no gain to switching to an alternate method.
I don’t disagree that some people get themselves in trouble with Excel, but, as I said before, so do IT departments and vendors when they design and build software (probably far more frequently than you might be aware). Better tools do not necessarily solve the problems of poor design or landmines waiting to go off.
Excel does shitty things to my data. I have never been able to figure out how to turn off that goddamn ‘feature’ that turns 1-1 into 1-Jan. And if I forget to format the cells beforehand and try to format them after, I get 43466, not 1-1. At least the data in my databases doesn’t change on me.
Oh, I forgot about that - textual data that just happens to comprise only numeric characters, with leading zeroes (e.g. phone numbers - they are not numbers - they are text) - mangled.