MS Excel / VBA help thread

Yeah that would have worked well, thanks so much.

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.

Separate tables, power bi / powerquery/power pivot?
Table for each entity type.

I need to do a form of traceability matrix for a project that involves many-to-one relationships

It's really quite incredible how cheaply Test-and-Trace tries to source consultation input on its work. :)

A bit off topic, but FAO SQL users - I’m wondering if there is a Evernote/ OneNote style application for SQL? Somewhere I can write code with all the formatting and keep my queries in a little library. If it would check syntax that would be amazing. Would have to be lightweight as I’m restricted in what I can install. Does this exist?

Confluence and text files is a bit painful.

Thanks

Notepad++ is nice - you can edit, with syntax highlighting - it has plugins for auto-formatting too.

You can edit in the application, and refresh in the SQL browser (I think TOAD does this, and the other one I usually use, but can;t remember the name of)

It has a portable version that can be run from a standalone folder, so doesn't need installing.

[Edit]
IIRC, it also has a plugin for connectors, but I've never felt the need to use them.

I didn't even realise you could set that to format for SQL, thanks. That's a help. I'll take a look at toad as well.

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.

Also Sublime Text
Prefer it to Notepad++ personally

Also Atom
Also Visual Studio Code
But those two are probably more than you need

In my limited experience I think of Powerquery etc as being good for extracting from stored data, but in this case there also needs to be a reasonably convenient way to populate data, including specifying the one-to-many relationships between entities from either end, which probably means a way of 'pick by names in a combo box, and store by ID'.

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.

Giving Sublime a try now, looks like it does the library thing

Also seen our software request system has visual studio in it so I'll bung a request in for that in case the portable version of Sublime has issues

thanks!

I'd go for Visual Studio Code rather than Visual Studio
The full blown Visual Studio is more than you need. VS Code is far more lightweight - comparable to Sublime Text, Atom etc.

It also has a portable option...
https://code.visualstudio.com/docs/editor/portable

Visual Studio Code rather than Visual Studio

Gone back, checked and both are available. Switched my request to VSC - thanks again.

Sublime has already saved me time on a BigQuery... query

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...

Any way of locking conditional formatting? Data to be received for different projects at different times - trying to protect against copy and pasting

Is it you entering the stuff or other people who are going to break it?

The only solution I found to this was a macro which re-applied the conditional formatting each time an edit was made. Not very elegant but seems to be the only option if you can't get people to paste values.

need to convert cells containing text of '03.2017 into Mar-17

mid, month, left, numbervalue some kind of mix of these and a bit of formatting should do it

a nice easy one for @Chalfie among others

=date(right(a1,4), left(a1,3),1)

the apostrophe is ignored

if it isn't ignored:
=date(mid(a1,2,2), left(a1,3),1)

ah! nice

in my defense i kind of tried that, but did the month bit first.

=DATEVALUE(REPLACE(REPLACE(A1,1,1,""),3,1,"/"))

edit:
=DATEVALUE(REPLACE(REPLACE(A1,1,1,""),2,1,"/"))

or even
=DATEVALUE(REPLACE(A1,3,1,"/"))

hmmm that is pretty good too

You just need someone else to tell you which is the most performant to use and go with that.

I don't know why as it always makes it more complicated but whenever I do this kind of thing I always use the end of the month rather than the start.

pivot tables

is it possible to use a pivot to get from the first layout to the second? i dont seem to be able to, but if it is then i can persevere.

so, columns become a row and are replaced by a current row

2 Attachments