Database recommendations
-
- Clardic Fug
- Posts: 216
- Joined: Wed Nov 20, 2019 6:17 pm
Database recommendations
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.
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.
Re: Database recommendations
Just do what everyone else does, and build what is essentially a database in Excel.sideshowjim wrote: ↑Fri Jan 03, 2020 7:47 pmI 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.
Re: Database recommendations
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:
Spoiler:
Move-a… side, and let the mango through… let the mango through
-
- Clardic Fug
- Posts: 216
- Joined: Wed Nov 20, 2019 6:17 pm
Re: Database recommendations
I've a hideously complicated Excel file, which contains the majority of stuff I'm doing in Access. The Excel file is version 1.dyqik wrote: ↑Fri Jan 03, 2020 10:08 pmJust do what everyone else does, and build what is essentially a database in Excel.sideshowjim wrote: ↑Fri Jan 03, 2020 7:47 pmI 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.
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...
Re: Database recommendations
My next step after Excel is usually Python, so I'm not going to be much help.
Re: Database recommendations
One of the recent graduates who sits near me generally doesn't bother with Excel but goes straight to Python.
Have you considered stupidity as an explanation
-
- Stargoon
- Posts: 143
- Joined: Mon Nov 11, 2019 4:36 pm
Re: Database recommendations
sideshowjim wrote: ↑Sun Jan 05, 2020 9:16 pmI've a hideously complicated Excel file, which contains the majority of stuff I'm doing in Access. The Excel file is version 1.dyqik wrote: ↑Fri Jan 03, 2020 10:08 pmJust do what everyone else does, and build what is essentially a database in Excel.sideshowjim wrote: ↑Fri Jan 03, 2020 7:47 pmI 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.
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...
- Pucksoppet
- Snowbonk
- Posts: 599
- Joined: Mon Nov 11, 2019 8:13 pm
- Location: Girdling the Earth
Re: Database recommendations
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...
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...
Re: Database recommendations
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?
Move-a… side, and let the mango through… let the mango through
-
- Clardic Fug
- Posts: 216
- Joined: Wed Nov 20, 2019 6:17 pm
Re: Database recommendations
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....
-
- Clardic Fug
- Posts: 216
- Joined: Wed Nov 20, 2019 6:17 pm
Re: Database recommendations
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.
Can't open Jpegs at the moment.
Re: Database recommendations
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.
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.
Move-a… side, and let the mango through… let the mango through
-
- Clardic Fug
- Posts: 216
- Joined: Wed Nov 20, 2019 6:17 pm
Re: Database recommendations
Thanks for that! I shall definitely look into BIRT and linking up with R!nekomatic wrote: ↑Sun Jan 12, 2020 11:55 pmOK 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.
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!
Re: Database recommendations
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?
Move-a… side, and let the mango through… let the mango through
- Brightonian
- Dorkwood
- Posts: 1443
- Joined: Mon Nov 11, 2019 3:16 pm
- Location: Usually UK, often France and Ireland
Re: Database recommendations
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.nekomatic wrote: ↑Tue Jan 14, 2020 9:11 amSteady 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?
- Bird on a Fire
- Princess POW
- Posts: 10137
- Joined: Fri Oct 11, 2019 5:05 pm
- Location: Portugal
Re: Database recommendations
Posts split from 'Oddly specific little rants megathread.
We have the right to a clean, healthy, sustainable environment.
Re: Database recommendations
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.
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.
The first ten million years were the worst.
And the second ten million, they were the worst too.
And the second ten million, they were the worst too.
- Rich Scopie
- Snowbonk
- Posts: 541
- Joined: Mon Nov 11, 2019 1:21 pm
Re: Database recommendations
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
https://www.microsoft.com/en-au/sql-ser ... ns-express
It first was a rumour dismissed as a lie, but then came the evidence none could deny:
a double page spread in the Sunday Express — the Russians are running the DHSS!
a double page spread in the Sunday Express — the Russians are running the DHSS!
Re: Database recommendations
You'd still want some kind of front end to do the data entry.Rich Scopie wrote: ↑Thu Jan 16, 2020 3:55 pmMaybe 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 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.
The first ten million years were the worst.
And the second ten million, they were the worst too.
And the second ten million, they were the worst too.
- Bird on a Fire
- Princess POW
- Posts: 10137
- Joined: Fri Oct 11, 2019 5:05 pm
- Location: Portugal
Re: Database recommendations
Thread moved from Relaxation Station
We have the right to a clean, healthy, sustainable environment.
- Rich Scopie
- Snowbonk
- Posts: 541
- Joined: Mon Nov 11, 2019 1:21 pm
Re: Database recommendations
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.Ben B wrote: ↑Thu Jan 16, 2020 4:31 pmYou'd still want some kind of front end to do the data entry.Rich Scopie wrote: ↑Thu Jan 16, 2020 3:55 pmMaybe 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 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.
It first was a rumour dismissed as a lie, but then came the evidence none could deny:
a double page spread in the Sunday Express — the Russians are running the DHSS!
a double page spread in the Sunday Express — the Russians are running the DHSS!
Re: Database recommendations
<weeps/>dyqik wrote: ↑Fri Jan 03, 2020 10:08 pmJust do what everyone else does, and build what is essentially a database in Excel.sideshowjim wrote: ↑Fri Jan 03, 2020 7:47 pmI 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.
-
- Clardic Fug
- Posts: 216
- Joined: Wed Nov 20, 2019 6:17 pm
Re: Database recommendations
I've had ideas about having web forms that add / recall data and the like.Rich Scopie wrote: ↑Fri Jan 17, 2020 8:38 amYes. 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.Ben B wrote: ↑Thu Jan 16, 2020 4:31 pmYou'd still want some kind of front end to do the data entry.Rich Scopie wrote: ↑Thu Jan 16, 2020 3:55 pmMaybe 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 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.
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..
Re: Database recommendations
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 ?
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 ?