# Microsoft Access **URL:** \ **Category:** The BBQ Pit\ **Created:** [April 19, 2012, 3:20am UTC](https://boards.straightdope.com/t/microsoft-access/619238 "2012-04-19T03:20:28Z")\ **Posts on this page:** 20\ **Page:** 2
**Author:** ![Sailboat](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/sailboat/32/461_2.png) [@Sailboat](https://boards.straightdope.com/u/Sailboat)\ **Post date:** [April 20, 2012, 1:05pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/21 "2012-04-20T13:05:04Z")
> [@Doctor](#): > > the worst thing I find about Access is that it’s “help” has to be the most arcane of any Microsoft product. I’d rather whip together an application in C# and install a MS SQL or MySQL database than try to figure out the logic of Access other than for utterly simple stuff. I know it can do a a lot, but I just can’t be bothered to learn the Access way of doing things. I’d probably end up buying a 3rd party book if a I really needed to learn it. This has been my experience. I taught myself rudimentary Access skills during a slow period at one job (with approval) using only Access itself (no textbooks). IMHO, the worst problem with Microsoft Access isn’t the “Access,” it’s the “Microsoft.” All MS applications have obtuse help; it seems to be a corporate philosophy. Maybe they save money on technical writing? Maybe they want to sell training courses, so they don’t want you to be able to figure things out on your own? The help is often maddeningly simple – it reads like a parody of help text: the **entire** help entry for “How do I do X” is typically “click the button that says X,” with no explanation of context, other options, or why you might want to use other similar functions instead. But there’s another part of it that bugs me – Microsoft products always seem to invent a new word or term for every common function. It’s like they think they can copyright the terms if they use different terms. So looking up anything in the help index of a Microsoft program is difficult if you don’t already know the term of choice. Many times I’ll be trying to find, to make up an example, “How do I attach x to y” and I’ll try “attach,” “append,” “link,” “add in,” “connect,” and so on, with no results at all, not even a suggestion like “to attach an item, see _blahblahblah_.” Finally I’ll blunder across some third-party website that reveals the Microsoft term for this is “e-partnering” or something equally bizarre. It’s like trying to learn a new language whose vocabulary is closely guarded by hostile native speakers. So I eventually got Access to work for fairly simple database queries, but every time I tried to learn something new, I was blocked and baffled at the start until I blundered across the Microsoftese words for what I was trying to do – only then could I start actually learning how to do it. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [April 20, 2012, 1:20pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/22 "2012-04-20T13:20:51Z")
> [@BigT](#): > > Finding duplicates very much is a database thing and not a spreadsheet thing. I haven’t used Access in a while, is it really that hard to have it find duplicates? It’s one of the things Access for which Access is really useful - ```auto SELECT SomeTable.SomeField, Sum(1) AS [Count] FROM SomeTable GROUP BY SomeTable.SomeField HAVING (((Sum(1))>1)); ``` ETA: That’s for duplicate rows in the same table, but duplicates of the same value in two different tables is just as easy. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [April 20, 2012, 1:25pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/23 "2012-04-20T13:25:12Z")
Assuming they actually _are_ duplicates, of course. Fuzzy matching is a whole 'nother thing. ---
**Author:** ![BobLibDem](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/boblibdem/32/3149_2.png) [@BobLibDem](https://boards.straightdope.com/u/BobLibDem)\ **Post date:** [April 20, 2012, 1:40pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/24 "2012-04-20T13:40:06Z")
Anyone else ever hear this mantra: “Someday everyone is going to stop using spreadsheets and start using Access”. My wife was told this by her instructor at an Excel class. Does this even remotely make sense? Spreadsheets are great, database programs are great (just that Access sucks ass in my opinion, I’m an Infomaker kind of guy). To think that all spreadsheet users are just going to pack it in and do everything in Access is nuts. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [April 20, 2012, 1:59pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/25 "2012-04-20T13:59:19Z")
I keep hearing the exact opposite - I talk about doing some really trivial data crunching job in Access and someone says “Oh, can’t you just do that in Excel by [some godawful arcane contorted longwinded malarkey that _still_ relies on a human scanning and manipulating the results]?” ---
**Author:** ![BobLibDem](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/boblibdem/32/3149_2.png) [@BobLibDem](https://boards.straightdope.com/u/BobLibDem)\ **Post date:** [April 20, 2012, 2:14pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/26 "2012-04-20T14:14:34Z")
> [@Mangetout](#): > > It’s one of the things Access for which Access is really useful - > > ```auto > > SELECT SomeTable.SomeField, Sum(1) AS [Count] > FROM SomeTable > GROUP BY SomeTable.SomeField > HAVING (((Sum(1))>1)); > > ``` > > ETA: That’s for duplicate rows in the same table, but duplicates of the same value in two different tables is just as easy. That’s pretty slick. I will remember this. ---
**Author:** ![PoorYorick](https://avatars.discourse-cdn.com/v4/letter/p/ecccb3/32.png) [@PoorYorick](https://boards.straightdope.com/u/PoorYorick)\ **Post date:** [April 20, 2012, 2:23pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/27 "2012-04-20T14:23:49Z")
> [@BobLibDem](#): > > Anyone else ever hear this mantra: “Someday everyone is going to stop using spreadsheets and start using Access”. My wife was told this by her instructor at an Excel class. Does this even remotely make sense? That has to be one of the more stupid things I’ve heard today. As other’s have said, spreadsheets do what spreadsheets do, and databases do what databases do, although there \*is \*some overlap. I love working with both programs. A coworker has a spreadsheet with thousands of lines of non-normalized data, and she’s using Excel to find particular records by multiple criteria. Her spreadsheet is a horrorshow of COUNTIF’s and DSUM’s. I’ve been begging her to give me about 4 hours to create an application in Access. That’s what databases are best at: finding things with multiple criteria. ETA: Access does have an auto-join option that you can turn on if you want. If you drag tables into the query window, Access will assume that the same column names that end in “ID” or “Num” or whatever imply a join and add it. It’ll even try to determine which is the master and which is the child. This is really handy sometimes when you design your tables with this in mind. You don’t have to turn it on, though. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [April 20, 2012, 3:17pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/28 "2012-04-20T15:17:53Z")
> [@BobLibDem](#): > > That’s pretty slick. I will remember this. That just tells you which values appear more than once in SomeField. If you want to deduplicate the table, it’s easier still - just do the grouping query without the count. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [April 20, 2012, 3:34pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/29 "2012-04-20T15:34:39Z")
> [@Sailboat](#): > > IMHO, the worst problem with Microsoft Access isn’t the “Access,” it’s the “Microsoft.” All MS applications have obtuse help; it seems to be a corporate philosophy. Maybe they save money on technical writing? Maybe they want to sell training courses, so they don’t want you to be able to figure things out on your own? The help is often maddeningly simple – it reads like a parody of help text: the **entire** help entry for “How do I do X” is typically “click the button that says X,” with no explanation of context, other options, or why you might want to use other similar functions instead. I completely agree. It’s really weird because there are other things they do where it’s clear they were thinking from the developers perspective. But when it comes to help it seems like the people in charge of that have zero concept of what a developer may be looking for to understand that particular item. Even today, if I am googling for an explanation of something, I always avoid any page from microsoft because I am almost always forced to continue looking for a more complete explanation. ---
**Author:** ![Clothahump](https://avatars.discourse-cdn.com/v4/letter/c/51bf81/32.png) [@Clothahump](https://boards.straightdope.com/u/Clothahump)\ **Post date:** [April 20, 2012, 4:21pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/30 "2012-04-20T16:21:36Z")
Wow. I’ve made a bucket of money off Access. I’ve taught courses in how to use it. And therein lies the problem. People don’t know how to use it properly. It’s not just a matter of dragging fields around on a report. It is a full-blown DBMS and some amount of intelligent design is required in creating the table structures. ---
**Author:** ![Yaxche](https://avatars.discourse-cdn.com/v4/letter/y/8dc957/32.png) [@Yaxche](https://boards.straightdope.com/u/Yaxche)\ **Post date:** [April 20, 2012, 5:34pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/31 "2012-04-20T17:34:14Z")
> [@Mangetout](#): > > However… I also hate it because it puts too much power in the hands of mortals - that is, I work in IT support now and a lot of what we’re dealing with is troubleshooting access application abandonware - someone created an application which a segment of the business subsequently came to heavily rely upon, then the creator of the application left (or died)… I’ll second this one. We had a buggy ODBC client upgrade, and since I’m the back-end database guy here, I found out just how much ‘production’ work is sitting around on peoples desktops in MS Access. A bit scary, actually. The smart ones at least put them out on the network share so there is some sort of backup, but that is hardly universal. I learned relational databases from Access, so I understand that it can be beneficial to entry level users, but now that I’ve seen what it does do the databases it runs against, I would never use it for anything that has any sort of performance requirement. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [April 21, 2012, 7:42am UTC](https://boards.straightdope.com/t/microsoft-access/619238/32 "2012-04-21T07:42:15Z")
> [@RaftPeople](#): > > If people are creating tables without using tools that reference a repository of pre-defined fields then you will run into this problem - but that is sloppy DB work. > > Any field with the same name as a field in a different table should contain the same type of information and should absolutely be linkable by name. Most of the large ERP systems I’ve worked on have been very consistent in this regard. The key word here is “should” and even that isn’t true in every situation. My concern was that it sounded a bit like his database system automatically linked them solely because they had the same name (might not have been true, as it was a bit vague), which is a pretty scary thought. If two tables are related, then explicitly define that relationship, period. Remember that ERP systems are not the same thing as database systems, even if they have one as their backend, so what might be standard in one world isn’t necessarily true in the other. _On using Natural Joins_ From Tom Kyte, the “Tom” of [asktom.oracle.com](http://asktom.oracle.com): > [@](#): > > i strongly, vehemently strongly, discourage the use of this “feature” called natural > joins…Never never never use this “feature” From PlanetMySQL: > [@](#): > > Thus I reckon that NATURAL joins are just nasty beyond belief, and I’ve been putting this to my students: if you ever find a dev using this, a) fire them on the spot and b) tell me about it! Of course there’s an aspect of humour in there, but it gets the point across. It’s a nasty, nasty beast. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [April 21, 2012, 7:47am UTC](https://boards.straightdope.com/t/microsoft-access/619238/33 "2012-04-21T07:47:34Z")
> [@Sailboat](#): > > IMHO, the worst problem with Microsoft Access isn’t the “Access,” it’s the “Microsoft.” All MS applications have obtuse help; it seems to be a corporate philosophy. While I completely agree with you regarding help on many of their products, it’s not universal. Their SQL Server help (BOL) is one of the more comprehensive pieces of goodness I’ve ever seen. For example, [the entry for CAST AND CONVERT](http://msdn.microsoft.com/en-us/library/ms187928.aspx), which is used to change datatypes and handle odd date formatting. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [April 21, 2012, 8:21am UTC](https://boards.straightdope.com/t/microsoft-access/619238/34 "2012-04-21T08:21:26Z")
> [@DMC](#): > > Remember that ERP systems are not the same thing as database systems, even if they have one as their backend, so what might be standard in one world isn’t necessarily true in the other. What I posted applies to databases, as well as the entire environment surrounding any system. Having inconsistency in named entities creates problems. > [@](#): > > _On using Natural Joins_ > > From Tom Kyte, the “Tom” of [asktom.oracle.com](http://asktom.oracle.com): And his reason for saying “never” use them is not due to crappy DB design, it’s due to ambiguities that can exist even in a good design - which is a valid point. But saying “never” use it is too strong - I see tons of situations daily in which it can be used safely - like everything, use it when appropriate. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [April 21, 2012, 8:40am UTC](https://boards.straightdope.com/t/microsoft-access/619238/35 "2012-04-21T08:40:54Z")
> [@RaftPeople](#): > > What I posted applies to databases, as well as the entire environment surrounding any system. > > Having inconsistency in named entities creates problems. It doesn’t even have to be inconsistencies. I’ve consulted on this stuff for decades, and I’ve seen more columns named things like “CreateDate” and “ModifiedDate” than I could possibly remember. Want to have the system automatically add those columns to the join you have between your PK and FK? Let me know how that works out for you. > [@](#): > > But saying “never” use it is too strong - I see tons of situations daily in which it can be used safely - like everything, use it when appropriate. Of course it **CAN** be used safely. If I personally handled every aspect of every database in our company, and if I signed a binding contract to remain an employee there (and to not bite the dust) until any such databases were retired, then I might consider using them. Okay, not even then would I do it, but I could probably be semi-safe under those completely unrealistic circumstances, as long as I never forgot that they were used even when rushing to get something out under time constraints. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [April 21, 2012, 4:54pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/36 "2012-04-21T16:54:15Z")
> [@DMC](#): > > It doesn’t even have to be inconsistencies. I’ve consulted on this stuff for decades, and I’ve seen more columns named things like “CreateDate” and “ModifiedDate” than I could possibly remember. Want to have the system automatically add those columns to the join you have between your PK and FK? Let me know how that works out for you. I have to (consulted for decades) and I don’t disagree with the example you just provided. But, I have used this type of feature numerous times in different environments over the last 30 years when I’m dealing with a well constructed DB, or even a subset of the tables in which it’s safe, and yes, it still requires review. Situations where it’s a useful feature: 1. If the join is created in some development environment/utility that shows the automatically created links so they can be reviewed 2. Or if it’s a natural join and the sql is not going into production where it can be impacted by later changes Probably 80% of the sql I write falls into category #2 (short life span, minutes to weeks) and here it’s just a matter of properly understanding the database and the feature. ---
**Author:** ![BTEzra04](https://avatars.discourse-cdn.com/v4/letter/b/9f8e36/32.png) [@BTEzra04](https://boards.straightdope.com/u/BTEzra04)\ **Post date:** [April 21, 2012, 6:15pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/37 "2012-04-21T18:15:08Z")
I can’t add much to the discussion right now, but as an undergrad enrolled in a database course at the moment…I like SQL Developer faaaar more than I liked Access, even if I’m writing the usual SQL in SQL developer. Access just makes me want to rip my hair out based upon its interface. I’ll have fun reading through this thread and learning from it later when I have the time; I’m really glad it’s been started. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [April 21, 2012, 8:13pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/38 "2012-04-21T20:13:02Z")
> [@RaftPeople](#): > > But, I have used this type of feature numerous times in different environments over the last 30 years when I’m dealing with a well constructed DB, or even a subset of the tables in which it’s safe, and yes, it still requires review. I agree if it’s well constructed. My concern (and I’m a fan of defensive programming) is that well-constructed DBs often become less so over time. > [@](#): > > Situations where it’s a useful feature: > > 1. If the join is created in some development environment/utility that shows the automatically created links so they can be reviewed > 2. Or if it’s a natural join and the sql is not going into production where it can be impacted by later changes > > Probably 80% of the sql I write falls into category #2 (short life span, minutes to weeks) and here it’s just a matter of properly understanding the database and the feature. That’s probably the difference and in fact, for ad hoc queries, I’d have no problem with that usage. Of course, ad hoc queries often turn into production queries pretty quickly. I typically manage teams that work primarily in the BI stack, where the lifespan is sometimes measured in decades, yet changes occur frequently, as new source data pops up all of the time. Hell, my current company just spend a few hundred million on a new company, which means that we’ll have massive additions to our warehouse in the near future. Short of micromanaging and poring over every line of code anyone on the team (and it’s a pretty large team, so that’s a lot of code) writes, I’m going to stick with a defensive foundation. It’s the same reason we typically put an abstraction layer (view) on top of our tables, even if it simply returns every column in a single table. Things change, sometimes frequently, and I don’t want breakages to be a common occurrence. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [April 21, 2012, 8:26pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/39 "2012-04-21T20:26:04Z")
> [@Kemanchi](#): > > I can’t add much to the discussion right now, but as an undergrad enrolled in a database course at the moment…I like SQL Developer faaaar more than I liked Access, even if I’m writing the usual SQL in SQL developer. > Access just makes me want to rip my hair out based upon its interface. When you say “SQL Developer”, are you referring to the free Oracle front end? If so, other than it being quite a bit slower than many of the pay-to-play options, I agree that it’s pretty decent. > [@](#): > > I’ll have fun reading through this thread and learning from it later when I have the time; I’m really glad it’s been started. If you find this stuff fun (I do), I’d recommend you learn both Oracle and SQL Server. Sounds like you have Oracle covered, but you can get a fully functioning Enterprise Edition of SQL Server 2012 (this is the one with all of the bells and whistles, including a rather large BI stack), for [about $60](http://www.amazon.com/SQL-Server-Developer-Edition-2012/dp/B007RFXQAM/ref=sr_1_2?s=software&ie=UTF8&qid=1334528501&sr=1-2). It’s called the Developer Edition, but other than the fact that you can’t legally use it for production work, it’s fully functional. In other words, you can build and test things all day long, you just can’t use it as a backend for your finished commercial product. Oracle has many great things going for it, but SQL Server does as well, and it comes with a lot more playthings in the core package. Knowing both will serve you well in your future, although you’re unlikely to be a true expert in both, as there’s just too much to keep up with. If you get really good, give me a shout. I might be hiring. 🙂 ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [April 21, 2012, 8:37pm UTC](https://boards.straightdope.com/t/microsoft-access/619238/40 "2012-04-21T20:37:21Z")
I’ve tried repeatedly to teach myself Access and even now taken a couple of pro-taught classes on it, and I still run back to my beloved Excel. Even if it probably takes me twice as long to figure out how to do what I want to do in Excel, I know what I’m doing all along the way. [Previous page](https://boards.straightdope.com/t/microsoft-access/619238.md?page=1) [Next page](https://boards.straightdope.com/t/microsoft-access/619238.md?page=3)