Nah, screw it. I just exported all my data tables to Excel Spreadsheets.
Those, at least, I can work with. Access? I can’t get that damned thing to do anything meaningful. Piss-awful interface, rotten design tools, rotten report tools, rotten, rotten, rotten. May ghouls gnaw on the oily bones of the bastards who created and maintain this filth-ridden application.
(Yeah, the fact that I’m too stupid to get it to work is also a factor… But, dammit, I can get Excel to do what I need!)
I remember waaaay back in the 90’s my dad bought Access to put our mailing list on. Even for just a simple mailing list I never really liked it…and this was coming from a 10-14 year old computer geek. Of course, I really didn’t know how or what it was supposed to do so that was probably part of the problem. Even now, (since I don’t use it) I have no idea what things like “mail merge” or “pivot table” are. I just learn them as I need them. At some point I just dumped the mailing list onto Excel and if I needed it now, I would just use whatever the OO offers in the way of a database manager, Base I think it’s called, never used it though.
ETA, I remember having to write some sort of macro and linking two tables together just to get it to find duplicate address. PITA that was. Ya know what was easier, I just sorted by address and scrolled through the 3000+ entries and kept my eyes open. Took about 20 minutes and got better results.
I got to do that once at my old workplace, sorting the list and using the “if” function, to look for any entry that was the same as the one just above it, and displaying a “1” if there was a match, and a blank otherwise. Easy-peasy, and it worked, and even a chowderhead like me could do it. Your way works too, since the human eye is really good at spotting things like that.
Where I used to work, we had “Progress” databases, which are a lot like “Oracle.” The programming language was nice and intuitive. I could do wonderful things with that. And my boss offered to buy me a personal copy, too…just before the company packed up and moved to Pittsburgh… Oops…
I’ve thought about “MySQL,” but it looks a little tough…
Anybody remember “Cornerstone” from, of all people, Infocom? Elegant DB, easy to use, friendly interface, intuitive design. I loved that’n.
I wish to hades there was a DB out there as nice as Cornerstone was!
I loved Access, if for lack of knowledge of anything else at the time.
In the mid-90s I had putzed around with a couple, but the concepts generally escaped me.
Then Office came with Access. One advantage was that it used a comfortable, somewhat familiar interface (BTW, let me use this opportunity to say “fuck the Ribbon”), so if you could move things around in Word you could use some of the form building tools–that sort of thing.
I got a basic idea of what was what and what it could (potentially) do. But I was still pretty much at the Hello World! stage. Then I shot my mouth off.
It was '98 or '99, and I was part of a small team doing a national property audit of the INS. Every field office had to be visited, spot checks of random items from their inventory, blah blah blah. In a staff meeting during the second year I started bitching about how awful the software we were using was at the time and what it could/should do.
You know where this is going …
… so with a new task in hand, I turned to and learned the shit out of Access. Not enough to qualify for a job as a database engineer, but enough to get it to jump through the hoops I wanted.
Good skills to have too in the post-undergrad years. Lots of people doing things on spreadsheets that after two minutes of looking at the craziness of their screen I could do for them a lot better in Access. Working as a temp created a ton of opportunities–and led to a slew of job offers (not as a programmer, but as a wow, we can use this guy in our office kind of way).
Hey, what’s the opposite of thread shitting?
Microsoft isn’t too keen on access either.
They would much rather sell you SQL Server or even give you SQL Server Express.
What they have been lax on is creating easier functions, methods, etc to interface Excel / Word with SQL.
Like you, I used to use Excel Workbooks to work with large data projects. Some of my workbooks approached 150 MB in size. That’s when I skipped over Access altogether and started SQL. It by no means is as intuitive as Excel. It took me a lot of time to gain some competency but SQL’s speed can lap Excel’s for some high crunching applications. .
I jumped over Access on the advice of a DBA friend who told me that Microsoft views Access as a dieing remnant application which is being slowly squeezed out by Excel’s growing ability to manage large data storage and SQL Server’s growing market.
Plus you can make more Deliverance jokes about Squeal Server.
Access is still very relevant as a desktop database application which is what it was designed to be. Not everyone can run SQL Server or Oracle at home but many people have the need for a true relational database and Access does the job very well. One of the biggest problems with Access is that it is a little too good. Businesses build full-blown applications out of it when they really should be looking at SQL Server on the backend. You end up with badly designed databases that are dog-slow after the requirements grow out of control over time. Access is still a great way to build a front-end user interface for SQL Server or even Oracle fairly easily.
I understand where the OP is coming from because I have heard it all before but I don’t agree with it. Your problem isn’t Access itself. It is that you don’t understand relational databases well enough yet. It isn’t an easy skill to pick up and can take years to learn well but it doesn’t take that long to learn the basics. You would have an even harder time if you tried to dive straight into SQL Server or Oracle yet those are vital applications that run much of the modern world. Access does a good job of dumbing down the relational database model for people with modest needs but there is still a big learning curve that can be frustrating if you just think of it as a different version of Excel (it isn’t at all; each has their distinct place).
Learning good relational database skills tends to be lucrative however and I would advise anyone with the aptitude to learn all they can. They can be used in many, many different types of jobs. I am a database developer with skills on multiple platform but I started with Access many years and still use it daily whenever it is the best tool for the job.
I agree with this. I used to hate Access because I didn’t understand it, I felt it was hard to use, and it never did what I wanted. Then I got a job that required me to extensively learn SQL, and I discovered how amazing Access really is. I mean, it’s a relational database that’s installed on nearly every Windows computer in the corporate world. You can sit down anywhere and do actual database work! And it’s a decent front-end to SQL. Amazing! Now I use it all the time, mostly when I’m travelling and I see someone struggling trying to do some complex analysis in Excel.
I loathe full-on Access applications though, and I see way too many of those. Part of my last job was going through a bunch of Access apps and converting them to the SQL/ASP apps that they should have been all along.
My workplace uses an Access-based program…it often crashes with no explanation, and can be rather sluggish (its database is huge). I’ve always enjoyed Access at home for managing addresses, but the program wasn’t available as part of a reasonably-priced Office package the last time I purchased a computer. I’m still using Access 2003 for now.
I’m a programmer and 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.
And, it is god-awful slow from my experience.
Oh, my GOD yes. We have a database at work that some moron kludged together in Access which makes me want to start feeding people into the woodchipper every time I have to use it. Multi-user database, on a server in England, used mostly be people in the US and Asia.
I could probably fly to England and go on a rampage in the IT department by the time that POS actually executes a command!
I should point out that I don’t dispute your claim.
I made the decision to go to SQL 5 years ago when I had been advised that Microsoft was downplaying Access. I don’t know that to be true but I haven’t seen Microsoft brag of any significant Access improvements with Office 2007 and Office 2010.
And some of my advisers were dopers. I posted a request in GQ asking about what Dbase programs, books, sources, etc should I aim for to get started in working with databases. The pros and cons of MySQL, SQL Server, Oracle, Access, etc were discussed and I came away with the feeling that Access would do the trick but it might not be around much longer (that part was wrong) or my Database needs might outgrow it (definitely true).
Well… I was a Progress database administrator for seven years, for a fairly large manufacturing corporation. I wrote the “serial number” DB in Progress, from the ground up. This was a large application that issued batches of serial numbers for requested part numbers – “We’re building 250 Model 29-T’s; give us a list of serial numbers to assign.” I maintained the part-number tables, the serial number tables, and the relationships between them.
No, I comprehend data structures pretty well. I just can’t make any of this work in Access…
Now, I’m willing to take my share of the blame for being stupid, a slow learner, stuck on details, etc. I can be remarkably stupid, no doubt upon’t.
But Progress was clear to me from the start, and Access is just a godawful effing muddle.
Just for one little example: dragging one table onto another table to create a link. Messy. Why not simply define a field as a key in another table with a field of the same name? In Progress, that’s a y/n function: “Is this field a key?” Bingo, yes, it is. The tables are now linked.
(I think Oracle does it the same way.)
Heck, you can even do master/detail tables in Excel, in a rough sort of form.
Another example: say I’m Scrooge, the moneylender. I have several people I lend money to, and thus the master table. Jim owes me fifty pounds. Jack owes me seventy five pounds. Then you go into the detail table. On August fifth, I lent Jim five pounds. On August seventh, I lent Jim six pounds.
Ideally, every time I add a new entry into the detail table – “Today, I lent Jack another four pounds” – the master table would automatically update – “Jack now owes me seventy nine pounds.”
In Progress, this is dirt easy. I’ve done it a hundred times.
In Access? I never figured out how. Access VBA has a “dsum” function, that gives the sum of all the specific entries in a field in a table. But it doesn’t work! You can’t say “Jack owes me dsum(detail, debts: Jack)” You should! It ought to work. It just mucking doesn’t!
One more little thing. Little tiny thing. In Excell, if I want to copy a cell to a whole range of cells, I can do that. Copy from a cell, then highlight a whole range, and paste. Access won’t! You can’t do that in table view in Access. You can copy one cell to one other cell. No pasting over highlighted ranges.
I’ve spent hours doing Ctrl-C, down, Ctrl-V, Ctrl-C, down, Ctrl-V, Ctrl-C, down, Ctrl-V. Hours! In Excel? Seconds. Ctrl-C, down, shift, down to end of section, Ctrl-V. Easy as chewing gum!
Anyway, this is the pit, so… Fiddle-Dee-Dee!
No, you’re not being stupid. You do seem to be trying to use Access for things it simply was never intended to be used for. It’s not a spreadsheet. There’s a reason why Access is Access and Excel is Excel. If you want to do spreadsheet things, use a spreadsheet.
Several reasons. First, Access uses visual interfaces for people who may find those to be extremely useful. I wish my SQl db had something of the sort at times, because when you’re dealing with three hundred tables it’s not easy to spot the connections
Second, and more important, there are very good reasons why you do NOT want to define table relationships based on column (field) names. Fields are often vaggue and in some cases based on the work of multiple people working in multiple places and multiple times and developing for multiple reasons. Name conventions should never be the basis of your table relationships.
Likewise, you’re trying to use Access to update the main data, which is a very bad idea. It’s not intended for this to be easy or convenient, because it’s not really to be done. Hell, many databases do not allow this at all - try doing that in a finance-based implementation of Dyamics GP, for instance.
Well, no, I really do want to do “database things.” I know the difference. And, yeah, there are things I’d like to do that Excel won’t let me do either. I could do it in a heartbeat in Progress (and I’m pretty sure Oracle is very similar…)
??? It’s completely standard in business! Key fields like “Customer” or “Order” or “Part-Number” etc. I have several years of experience in the Progress database, in the QAD erp system. All the key fields have common English names.
Well, again, that’s how it’s done in Progress and QAD. You can define convenient “computed” fields, which are recalculated on the fly. Some are sums, or subtotals. Others are even more obvious, such as “Date Last Updated.” Whenever any data in a table is changed, that field is automatically updated to “Today.”
No, really: my beef isn’t with databases in general. It’s just with Access, which I can’t manage to work with.
Anyway…it’s my pit, and I stand by it! Rowrbazzle! 
I’m guessing that smiling bandit is talking about what are known as “Natural Joins”, which are indeed the devil. There is nothing wrong with having identical names in different tables that are related. There IS something wrong with having the database platform assume they are related simply because they share a name.
Not knowing anything about the Progress database platform, I wasn’t sure if it was simply a difference in terminology, but your post does seem a bit foreign to those of us who work in the Oracle and SQL Server worlds. I think your use of the term master table caused him to think of one of the metadata tables used by most systems. I certainly found it hard to figure out exactly what you meant.
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.
“Master” table is a pretty standard term in database systems (probably around since the 60’s or 70’s).
In most enterprise systems there are hundreds to thousands of tables that hold entities (like customers, items, etc.) that are called “master” tables as they are not transactional tables (order header, order detail, inventory transaction, etc.)
I have a love-hate relationship with Access. I love using it - it’s an enormously useful tool for throwing data around (which can be done using both lesser and greater applications, but not nearly so easily, IMO). I’ve used Access to create several highly-functional business applications in the past - it’s extremely flexible as a rapid application development solution.
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)…
…and a couple of years later, the application inexplicably breaks, or needs new features, or won’t run on the latest version of Access - and it’s very hard (sometimes impossible) to fix - either because of the obscure methods used in the code, or because the author locked down the database and it can’t be edited or opened in design mode.
I also kinda wish MS had just implemented standard SQL in Access, as that’s where I learned SQL, and sometimes this makes me look silly when I’m talking to DB professionals and I use a bit of Jet-specific terminology.
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?