Is a relationtional database / Microsoft Access our best bet here?
September 26, 2018 9:56 AM   Subscribe

I have been charged with creating an Access database that will contain a significant amount of text data but I'm not sure, after digging in, this is the way to go. Hope me!

This is what the text data I've got looks like:

Thing 1 (#) will have multiple Major Objectives (#.#). Each Major Objective will have multiple Sub-Objectives (#.#.#). Each Sub-Objective will have multiple Activities (#.#.#.#) and each Activity will have multiple Tasks (#.#.#.#.#). My initial plan was to make a fillable form, duplicate it, and fill out one for each Task (because these can and will change over time). Those would then be attached to each Activity. Essentially, this is a GIANT project with tasks for hundreds of folks to work on.

I'll need to be able to run reports from this database, as well, and this needs to be something a layperson can use to access each form that contains the Tasks. I think that's going to be a stretch using an Access database.

I'm working from an Access database right now that contains most of this information but databases are new to me, my brain is not at all grasping these relationships, and I'm struggling to grasp how Access even works to begin with. This usually isn't an issue for me -- I can play around with something or research it hard for a bit and I'll get it. That's not happening here and I've been working this for about 2 weeks now.

I played around with a demo of Obvibase last night and to be honest, it seems much easier and better suited to this project than Access. It's MUCH cleaner, given that I'm dealing with nothing but long strings of text and short number sequences (used to define the Activities and Tasks, for example) but it doesn't offer the opportunity to run reports so that's a deal breaker.

I *might* be able to talk my contractor into paying for something but that's doubtful even though this is technically going to be used by folks involved in government work. That said, at this point, I'm finding it unlikely Access is my best bet here for organizing all this data and linking it to each other when necessary.

Any suggestions? Keep in mind, I'm new to this but I'm a quick learner (usually) and I'm willing to put significant time into figuring out whatever program / service we decide to use given that the entirety of this work which I'll be doing over the next five years will stem from this database.
posted by youandiandaflame to Grab Bag (14 answers total) 4 users marked this as a favorite
May I ask why you were tasked with building a database to perform this activity? Project management systems suitable for managing tasks for hundreds of people can easily be purchased.

I encourage you to do a build/buy analysis and see if one of the many pre-packaged solutions will meet your needs. Once you factor in labour, maintenance, hardware, etc a home-brewed system is often more expensive.
posted by crazycanuck at 10:06 AM on September 26, 2018 [5 favorites]

The database was halfway built by my employer but the reasoning behind it was to use it as a way to organize all this data and tasks. I've managed large, expensive projects before, with multiple lines of effort, but I did it alone and had my own system for it so I've never used something purchased for this. It's necessary now because there will be many people using this database.

If Access is NOT the right fit here, I'm looking for specific recommendations, if folks have them! I know little, if anything, about project management systems or programs, so "hey, try Totally Made Up Awesome PM Program" would be super helpful. :)
posted by youandiandaflame at 10:22 AM on September 26, 2018

This usually isn't an issue for me -- I can play around with something or research it hard for a bit and I'll get it. That's not happening here and I've been working this for about 2 weeks now.

This is because you are being asked to build software, here, and like... a 12-week full-time boot camp gets you qualified enough to build software under the supervision of other more senior developers for several years, after which point you might genuinely be competent enough to build this. This is not a slight on you or anything, this is just saying that the thing you're trying to do is skilled labor that many people put a lot of time and effort into learning to do, and you shouldn't feel like you're not adequate because you can't build this well any more than you should feel inadequate that you can't go out today and safely replace all your home's wiring. Using Access for something like this is like trying to replace the licensed electrician with a Youtube video. Yes, it enables you to do it yourself, but you almost certainly still shouldn't.

Are you acting in the PM role for this project? Or, if not, what role do you actually have? Do these hundreds of people need access to this, or is it just something where you or a small handful of people will be using it? When you say you want reports, what kind of reports? That kind of detail will make it easier to propose alternatives.
posted by Sequence at 10:56 AM on September 26, 2018 [2 favorites]

tblMajor, tblSub, tblActivity, tblTask, fine fine no problem ... whoa HOLD UP layperson data entry. I've been building MS Access databases for years. Do not underestimate how much work and behavior research and observation you need to put in to make a set of easily usable forms.

I agree with crazycanuck - maybe overlook the sunk cost and find something off-the-shelf?
posted by turkeybrain at 11:06 AM on September 26, 2018 [3 favorites]

Not to pile on, but yeah, the right answer here is "buy something". What your employer has put in front of you would be like a freight shipping company asking a random employee to design and manufacture the trucks they're going to use instead of contacting the business sales department of the local dealerships.


I *might* be able to talk my contractor into paying for something but that's doubtful even though this is technically going to be used by folks involved in government work.

Government work frequently has requirements and standards around project tracking, cost accounting, etc. Building this in-house means you're going to also have to have in-house expertise on every relevant statute, potentially get your solution certified, etc.

What you're being asked to do is not reasonable and there really isn't a good answer to what to use instead of Access that would make it reasonable.
posted by tocts at 11:25 AM on September 26, 2018 [1 favorite]

Makes me feel a little better, Sequence. Thanks. :)

I'm not the PM here but I will be helping to assemble this database and I'll likely be the only one adding to it and maintaining it. Hundreds of people will need access to the Tasks which will essentially function as project plans, if that makes sense -- a list of things that need done. But as our database currently stands, they'll have to navigate through multiple tables to get there and it's too damn clunky right now to be worth much at all.

(Trying not to threadsit but I'm here to answer questions if it helps folks recommend specific stuff!)
posted by youandiandaflame at 11:30 AM on September 26, 2018

Huge companies with thousands of employees and hundreds of employees per project, e.g. Johnson & Johnson, use MS Project for this. I'd tend to look at that, or some other project management-specific solution. Agreeing with everyone that an Access database sounds totally wrong-headed here.
posted by nosila at 11:33 AM on September 26, 2018

Atlassian’s Jira is another solution. It will require some (maybe a lot) of customization, but it is used for managing many kinds of tasks with many child, grandchild etc tasks, and many characteristics. I suspect that your time is better spent configuring it (or similarly scalable software) than building something in Access.
posted by chesty_a_arthur at 11:57 AM on September 26, 2018

Computer Associated Rally is another option for a purchasable application. CA Rally
posted by The_Vegetables at 12:22 PM on September 26, 2018

Also if you do want to build something in house using MS Access, remember that MSAcess is really just a database product with a basic gui for building forms so you can build your customer-facing front end using web-based tools. And if you have database skills, you can build a lot (not everything, but a lot) programmatically.
posted by The_Vegetables at 12:25 PM on September 26, 2018

posted by j_curiouser at 4:19 PM on September 26, 2018

The major weakness of Access is it is a file-based database system, and not a client/server one. This means that it handles multiple connections poorly, and, for example, is absolutely unsuitable for use as a backend to a web site.

Hundreds of people will need access to the Tasks which will essentially function as project plans
If you expect to have more than a few concurrent users, it's just not a great solution. Hundreds? Project failure is very likely.
posted by thelonius at 4:25 PM on September 26, 2018 [2 favorites]

If data security is an issue, Access is not the solution.
posted by SemiSalt at 8:07 AM on September 27, 2018 [1 favorite]

MS Access is a very poor choice from all aspects (security, data reliability, multi-user, cross-platform compatibility, maintenance cost, mobile access, remote access, upgrades, patching, etc.). It should be avoided like the plague.

You should use an existing project management system, examples:
* Jira
* Asana
* Favro
* Smartsheet
* Basecamp
* Zoho Projects (low cost option)
* Redmine (free on-site solution)
posted by Sharcho at 12:00 PM on September 27, 2018

« Older threes a' crowd-sourced   |   How to help a friend? Newer »
This thread is closed to new comments.