Scrutable

I thought that MS Access would be really helpful in a work thing I'm setting up.

I still think it would be. But trying to get it to actually do what I want it to feels a lot like randomly running full-speed into brick walls on the vain hope that I will eventually find a doorway.

sideshowjim wrote: Fri Jan 03, 2020 7:47 pm I thought that MS Access would be really helpful in a work thing I'm setting up.

I still think it would be. But trying to get it to actually do what I want it to feels a lot like randomly running full-speed into brick walls on the vain hope that I will eventually find a doorway.

Just do what everyone else does, and build what is essentially a database in Excel.

Access is perfect for the set of use cases in between ‘easier to do it in Excel, to be honest’ and ‘really needs a proper database, even if it’s just SQLite’.

Spoiler:

dyqik wrote: Fri Jan 03, 2020 10:08 pm

sideshowjim wrote: Fri Jan 03, 2020 7:47 pm I thought that MS Access would be really helpful in a work thing I'm setting up.

I still think it would be. But trying to get it to actually do what I want it to feels a lot like randomly running full-speed into brick walls on the vain hope that I will eventually find a doorway.

Just do what everyone else does, and build what is essentially a database in Excel.

I've a hideously complicated Excel file, which contains the majority of stuff I'm doing in Access. The Excel file is version 1.

The Access database is version 2, because I thought it would make it much simpler to use once it's up and running!

I can't help but think I've gone very very wrong somewhere. Especially as I've now brought an Access 2016 book that's the size of a phone-book...

My next step after Excel is usually Python, so I'm not going to be much help.

dyqik wrote: Sun Jan 05, 2020 9:46 pm My next step after Excel is usually Python, so I'm not going to be much help.

One of the recent graduates who sits near me generally doesn't bother with Excel but goes straight to Python.

sideshowjim wrote: Sun Jan 05, 2020 9:16 pm

dyqik wrote: Fri Jan 03, 2020 10:08 pm

sideshowjim wrote: Fri Jan 03, 2020 7:47 pm I thought that MS Access would be really helpful in a work thing I'm setting up.

I still think it would be. But trying to get it to actually do what I want it to feels a lot like randomly running full-speed into brick walls on the vain hope that I will eventually find a doorway.

Just do what everyone else does, and build what is essentially a database in Excel.

I've a hideously complicated Excel file, which contains the majority of stuff I'm doing in Access. The Excel file is version 1.

The Access database is version 2, because I thought it would make it much simpler to use once it's up and running!

I can't help but think I've gone very very wrong somewhere. Especially as I've now brought an Access 2016 book that's the size of a phone-book...

My unique skill at work used to be that I'd use Access to automate time consuming but repetitive accountancy tasks, imaging my surprise when getting a contract on this basis and being told by IT that they refused to give me anything other than Access Viewer...

I'll ask a really stupid and naïve question here.

Instead of using Access, why not use SQLite?

It might be to do with (the lack of) integration with Microsoft Office, but if the issue is needing a cheap-n-cheerful database...

I think we need to understand the requirements a bit better. Assuming the data can be stored as a bunch of tables, do you have complex business logic you need to implement or is it more about putting a user interface on it for your colleagues?

nekomatic wrote: Wed Jan 08, 2020 12:27 am I think we need to understand the requirements a bit better. Assuming the data can be stored as a bunch of tables, do you have complex business logic you need to implement or is it more about putting a user interface on it for your colleagues?

It's a tracking system for audits performed in the department. So there's a table with all the audits performed on it (title, date started, text from introduction, that kinda gubbins). Then there's another table with all the actions performed on it (many to one, each completed audit should have a bunch of actions performed afterwards). Then there's another one with conferences and the like where people might like to show off what they did.

So what I've been trying to do (and mostly succeeding!) is to A- Highlight stuff that is overdue, B- have a quick way of emailing the people responsible a "get on with it" email, C- Send the other people that look at this stuff at a trust rather than a department level a regular update, D- Some graphs and stuff would be nice, E- Spit out a nice report for the end of year report.

I'm probably gonna be the only person using it, but it would be nice if someone else could if I have time off. Also, it would be nice to try and link to sharepoint (URGH) at some point, but that's a whole other fetid story.

Now I have managed to get R studio installed on an NHS machine (though half the packages I try and use get blocked), I had to ask around a lot to get Access installed. NO-one else in the department has it, so does kinda limit how much anyone else can use it!

May look into SQLlite though....

ALSO, integration into office? Can't set up a form in Word, can't use macros in word or excel... Gotta love NHS IT.

Can't open Jpegs at the moment.

OK so it sounds like your data input process is fairly straightforward and the data itself is not too huge or complex. In that case I would have thought since you have Access you might as well keep using it for those parts unless there's a difficulty that I haven't appreciated?

The rest of what you describe sounds like it would fall into the category of reporting. The traditional approach to that would be using something like Crystal Reports (costs, but you might have licences) or BIRT (free) but you can do a lot in Excel using the database query capabilities, even if you can't run any macros - you can get it to fill down one or more columns of formulae next to each row it reads from the database, which you could then use to highlight overdue actions for example.

If you're familiar with R you can probably do anything else you need to there, as long as you can read from the Access database successfully - I haven't done any database stuff in R but based on a quick search this may be of help.

nekomatic wrote: Sun Jan 12, 2020 11:55 pm OK so it sounds like your data input process is fairly straightforward and the data itself is not too huge or complex. In that case I would have thought since you have Access you might as well keep using it for those parts unless there's a difficulty that I haven't appreciated?

The rest of what you describe sounds like it would fall into the category of reporting. The traditional approach to that would be using something like Crystal Reports (costs, but you might have licences) or BIRT (free) but you can do a lot in Excel using the database query capabilities, even if you can't run any macros - you can get it to fill down one or more columns of formulae next to each row it reads from the database, which you could then use to highlight overdue actions for example.

If you're familiar with R you can probably do anything else you need to there, as long as you can read from the Access database successfully - I haven't done any database stuff in R but based on a quick search this may be of help.

Thanks for that! I shall definitely look into BIRT and linking up with R!

The difficulties are basically that I haven't touched access since a couple of lessons on it in 1999 that I wasn't paying much attention through... Getting there step by step though!

sideshowjim wrote: Mon Jan 13, 2020 9:32 pmI shall definitely look into BIRT

Steady on, I didn’t say I recommended it

It sounds like your path of least effort is probably to sort out Access as the front end for data entry and browsing, then get R to read from it for the other stuff. Do your colleague(s) who might also need to operate it also know R?

In line with the new anti-megathread policy I’ll ask mods to split this discussion out, just for tidiness?

nekomatic wrote: Tue Jan 14, 2020 9:11 am

sideshowjim wrote: Mon Jan 13, 2020 9:32 pmI shall definitely look into BIRT

Steady on, I didn’t say I recommended it

It sounds like your path of least effort is probably to sort out Access as the front end for data entry and browsing, then get R to read from it for the other stuff. Do your colleague(s) who might also need to operate it also know R?

In line with the new anti-megathread policy I’ll ask mods to split this discussion out, just for tidiness?

Some years since I've used it, but I found the report wizard(?) in Access was quite good. It would align things reasonably sensibly so that you'd only have to do small bits of fiddling about to make it presentable. No idea about how it compares with what's available via R though.

Posts split from 'Oddly specific little rants megathread.

I've done tons of Access stuff. It's by no means a bad product, but it does have limitations, and outside of a fairly small envelope, it's going to struggle.

If you're the only person entering it, then Access is fine as a data entry/edit tool.

If you want several people to input the data, and there is a possibility that sometimes 2 or more people will be entering data at the same time, you will almost certainly run into problems. While it claims to allow multi user simultaneous updates, IME it almost never works and you end up with conflicts.

For reports, I think Excel ought to be absolutely fine for your purposes, and can link to an Access data source. BIRT is awful, I would avoid it.

Maybe get a proper database. SQL Server Express 2017, or SQL Server 2017 Express LocalDB. (Both free)

https://www.microsoft.com/en-au/sql-ser ... ns-express

Rich Scopie wrote: Thu Jan 16, 2020 3:55 pm Maybe get a proper database. SQL Server Express 2017, or SQL Server 2017 Express LocalDB. (Both free)

https://www.microsoft.com/en-au/sql-ser ... ns-express

You'd still want some kind of front end to do the data entry.
You can link Access to SQL tables, and that works a lot better for multi user access than tables contained within Access itself.
Or, it's fairly easy to write a web form to write into the SQL tables, which is much easier to deploy to users, as it just uses the browser.

Ben B wrote: Thu Jan 16, 2020 4:31 pm

Rich Scopie wrote: Thu Jan 16, 2020 3:55 pm Maybe get a proper database. SQL Server Express 2017, or SQL Server 2017 Express LocalDB. (Both free)

https://www.microsoft.com/en-au/sql-ser ... ns-express

You'd still want some kind of front end to do the data entry.
You can link Access to SQL tables, and that works a lot better for multi user access than tables contained within Access itself.
Or, it's fairly easy to write a web form to write into the SQL tables, which is much easier to deploy to users, as it just uses the browser.

Yes. These were my thoughts on the front end as well. Or even use Excel as the front end. Depends how much data manipulation is needed.

dyqik wrote: Fri Jan 03, 2020 10:08 pm

sideshowjim wrote: Fri Jan 03, 2020 7:47 pm I thought that MS Access would be really helpful in a work thing I'm setting up.

I still think it would be. But trying to get it to actually do what I want it to feels a lot like randomly running full-speed into brick walls on the vain hope that I will eventually find a doorway.

Just do what everyone else does, and build what is essentially a database in Excel.

<weeps/>

Rich Scopie wrote: Fri Jan 17, 2020 8:38 am

Ben B wrote: Thu Jan 16, 2020 4:31 pm

Rich Scopie wrote: Thu Jan 16, 2020 3:55 pm Maybe get a proper database. SQL Server Express 2017, or SQL Server 2017 Express LocalDB. (Both free)

https://www.microsoft.com/en-au/sql-ser ... ns-express

You'd still want some kind of front end to do the data entry.
You can link Access to SQL tables, and that works a lot better for multi user access than tables contained within Access itself.
Or, it's fairly easy to write a web form to write into the SQL tables, which is much easier to deploy to users, as it just uses the browser.

Yes. These were my thoughts on the front end as well. Or even use Excel as the front end. Depends how much data manipulation is needed.

I've had ideas about having web forms that add / recall data and the like.
Minor problems:-
1 I dunno how
2 I haven't really got the time or inclination to learn
3 It would have to go through Internet Explorer running on Windows 7
4 The only places I could add pages would be on a extremely out-of-date version of sharepoint

Would however solve the problem of no-one else having access and so being unable to use it though..

does this data need to be secure or can it be dumped somewhere public ?

e.g. are we talking about personal or financial records, or is it some non-confidential experimental data you just don't want people to be able to edit unless you've given them a password ?