-
I realise the solution is a database, not Excel.
Praise be.
Right now all we have is desktop Access. Which I'm deeply mistrustful of by reputation. So the actual question is - what's likely to go wrong if I do?
An Access DB is made up of two parts: The front-end (forms etc) and the data store which is a file that's accessed via ODBC, with the Microsoft Jet driver.
Once you've got it working in access, you can swap out the Jet DB for a SQL Server connection. I've never needed to do this, but I've heard it can be less painful than migrating from Py+SQLite to Py+MySQL, since Jet behaves more like a conventional db.
Then while it's running in MS SQL server with your Access front-end, build the strategic front end in something else.
Emyr-
Having done a bit of reading, the default is to store everything as a single file, but I have overridden that.
I have decided to deliver it in Access as the only available tool in the given timescale, but caveat it as a working model/proof of concept which isn't necessarily scalable to a proper DB. I'm fairly confident of the basic data model, though.
Some of the business problems described by the data actually relate to insufficiently controlled end-user developed applications (EUDAs if you must), which is ironic, but I think could actually be used as political leverage for someone else to find a way of getting it into SQL Server/similar as you suggest.
-
So the official answer from our Data/Analytics dept was 'have you considered using Jira?'. I suppose it does implement a way of setting up one:many relationships...
chez_jay
I need to do a form of traceability matrix for a project that involves many-to-one relationships between at least 4 entity types (business goals, initiatives, problems, systems). Maybe a few hundred entities in total. It's new data to be keyed in, not from existing sources. I suspect at least one of those relationships will be many-to-many, once I've looked hard enough.
I realise the solution is a database, not Excel. Previously I've been able to call in favours from IT people (I'm not in IT) and get a small MSSQL or Oracle application put together.
Right now all we have is desktop Access. Which I'm deeply mistrustful of by reputation. So the actual question is - what's likely to go wrong if I do?
If this was a personal project, I'd probably do it in Python+SQLite+Flask (or maybe Django). But I don't have those at work.
I guess Sharepoint's sort-of-database tables might possibly be able to do it, but Sharepoint.