Databases and reporting, the non-technical person's guide
July 8, 2015 7:50 AM Subscribe
I'm looking for suggestions for databases and reporting tools that can deal with relatively small amounts of data, don't cost loads of money, and don't require super technical expertise. Details beyond.
I work at a teeny tiny charity and I run a data collection gathering data from ~450 services across the UK. Data is submitted annually by email, mostly through Excel forms which have named fields, which get into the 15 year old Access 2007 database via a bit of VBA that I inherited and don't understand. Some of the data comes in on PDFs or Word forms, and if I'm very lucky people print the spreadsheet and post it to me. These have to be manually entered at my end.
The database has evolved over time and so has lots of legacy fields that I don't need any more, tons and tons of ad hoc queries clogging it up, records for services that aren't relevant or don't exist any more but apparently can't be deleted without everything falling apart, all sorts of joys. That doesn't mean it needs to be scrapped entirely (which is my first instinct) but it does need some serious tidying up. I've come into this role from a social science background rather than a technical one; I can do statistical analysis but I don't have coding experience or much technical knowledge about databases so I don't know what our options are and whether Access is actually the best option there is.
There are reports that go back to services after the data collection is done. These are also coded with VBA that I didn't write and don't understand. At the moment, the code gathers up data, does some calculations, produces some charts in Excel that then get pasted into Word, that then get PDFed and mail merged to be sent out. I've used Crystal Reports in previous roles which does all that functionality only without me needing to know the code underneath but I don't know if that's the best tool for the job - it's just the main one I've used.
In an ideal world, we'd have a web form for data entry that connected to the database, so services could type in their data directly. The web form would have checks for where figures look weird so I don't spend half of my life emailing people back asking them if they really meant 200 instead of 20. Then there'd be a reporting tool that could generate the reports and let me redesign them without needing to faff around writing the whole thing directly in code. Is that possible on a tiny budget? Or is it just a pipe dream? And if it is possible, what software should we be looking at?
Other constraints:
*I don't think we can use anything based in the cloud, it's going to need to be hosted on our server because of the nature of the data.
*I have almost no budget for this this year, and no guaranteed budget next year, so it needs to be a super cheap solution.
(I suspect there are going to be people who pop up and say 'just learn VBA!' which would definitely be one solution, but only if you want to come and do some of the million bits of work I also do alongside this data collection so I have the time to do that. Otherwise assume that's a no-go. I'm doing the best I can but I was hired for my reporting and insight skills, and the technical side of things has fallen on me through a series of unfortunate events.)
tl;dr: what are your database and reporting recommendations for somewhere that has no money and no in-house technical expertise?
I work at a teeny tiny charity and I run a data collection gathering data from ~450 services across the UK. Data is submitted annually by email, mostly through Excel forms which have named fields, which get into the 15 year old Access 2007 database via a bit of VBA that I inherited and don't understand. Some of the data comes in on PDFs or Word forms, and if I'm very lucky people print the spreadsheet and post it to me. These have to be manually entered at my end.
The database has evolved over time and so has lots of legacy fields that I don't need any more, tons and tons of ad hoc queries clogging it up, records for services that aren't relevant or don't exist any more but apparently can't be deleted without everything falling apart, all sorts of joys. That doesn't mean it needs to be scrapped entirely (which is my first instinct) but it does need some serious tidying up. I've come into this role from a social science background rather than a technical one; I can do statistical analysis but I don't have coding experience or much technical knowledge about databases so I don't know what our options are and whether Access is actually the best option there is.
There are reports that go back to services after the data collection is done. These are also coded with VBA that I didn't write and don't understand. At the moment, the code gathers up data, does some calculations, produces some charts in Excel that then get pasted into Word, that then get PDFed and mail merged to be sent out. I've used Crystal Reports in previous roles which does all that functionality only without me needing to know the code underneath but I don't know if that's the best tool for the job - it's just the main one I've used.
In an ideal world, we'd have a web form for data entry that connected to the database, so services could type in their data directly. The web form would have checks for where figures look weird so I don't spend half of my life emailing people back asking them if they really meant 200 instead of 20. Then there'd be a reporting tool that could generate the reports and let me redesign them without needing to faff around writing the whole thing directly in code. Is that possible on a tiny budget? Or is it just a pipe dream? And if it is possible, what software should we be looking at?
Other constraints:
*I don't think we can use anything based in the cloud, it's going to need to be hosted on our server because of the nature of the data.
*I have almost no budget for this this year, and no guaranteed budget next year, so it needs to be a super cheap solution.
(I suspect there are going to be people who pop up and say 'just learn VBA!' which would definitely be one solution, but only if you want to come and do some of the million bits of work I also do alongside this data collection so I have the time to do that. Otherwise assume that's a no-go. I'm doing the best I can but I was hired for my reporting and insight skills, and the technical side of things has fallen on me through a series of unfortunate events.)
tl;dr: what are your database and reporting recommendations for somewhere that has no money and no in-house technical expertise?
I worked in Access databases for about ten years and know very little VBA. I would guess that there are many things being done in VBA that can be done a different way. I'd stick with Access because so may people use it that you can find help easier and there are plenty of tutorials on the web using the free sample Northwind or AdventureWorks databases. Inheriting a database without documentation is not fun, but it can be a learning experience.
Things to try if you haven't:
Make a copy of the database, rename it as Testing.accdb and play with it. When you delete something that breaks another thing, go back and figure out why Query A is using Table B and if there is another source for the same info.
Standardize the data collection (possibly delegating the verification/conversion into Excel to another person) by providing an Excel template that people can fill out or that you can fill out for people.
Try recreating the reports/charts yourself as if you didn't have the old version and then compare your results to the old version.
When you are stuck on how to do something, search the web for how to do it and include "Access 2007" in the query. You'll find several q&a sites show up often enough in your results that you end up going directly there.
we'd have a web form for data entry that connected to the database
I have no experience working with them, but Access has web databases now. I would suggest that if you go down that route, you have a completely separate database that interfaces with the web for data colection and then you import the data from that db into your reporting db.
posted by soelo at 9:00 AM on July 8, 2015 [1 favorite]
Things to try if you haven't:
Make a copy of the database, rename it as Testing.accdb and play with it. When you delete something that breaks another thing, go back and figure out why Query A is using Table B and if there is another source for the same info.
Standardize the data collection (possibly delegating the verification/conversion into Excel to another person) by providing an Excel template that people can fill out or that you can fill out for people.
Try recreating the reports/charts yourself as if you didn't have the old version and then compare your results to the old version.
When you are stuck on how to do something, search the web for how to do it and include "Access 2007" in the query. You'll find several q&a sites show up often enough in your results that you end up going directly there.
we'd have a web form for data entry that connected to the database
I have no experience working with them, but Access has web databases now. I would suggest that if you go down that route, you have a completely separate database that interfaces with the web for data colection and then you import the data from that db into your reporting db.
posted by soelo at 9:00 AM on July 8, 2015 [1 favorite]
Define "tiny budget". Salesforce has (or used to) lower pricing for non-profits. Not sure if the non-profit stuff is available in the UK though.
posted by LoveHam at 12:39 PM on July 8, 2015 [1 favorite]
posted by LoveHam at 12:39 PM on July 8, 2015 [1 favorite]
Response by poster: Tiny budget = ~£10k this year to do something new but no guarantee of funds in future years (though I can build in costs to future funding bids, there's just no certainty of getting the money).
There are 6 forms, services may fill in anywhere between 1 and 6 of them. The longest one has about 150 fields, the shortest about 100, but lots of the fields are the same on each form so it would be easy to duplicate and edit them.
Hearing that you can work with Access without knowing VBA is heartening, thank you!
posted by theseldomseenkid at 12:08 AM on July 9, 2015
There are 6 forms, services may fill in anywhere between 1 and 6 of them. The longest one has about 150 fields, the shortest about 100, but lots of the fields are the same on each form so it would be easy to duplicate and edit them.
Hearing that you can work with Access without knowing VBA is heartening, thank you!
posted by theseldomseenkid at 12:08 AM on July 9, 2015
This (Management Informations Systems) is one of my jobs, I am in fact part of the MIS team at a large consulting firm.
This is very much the sort of thing which we get hired to do, but of course we are very probably out of your budget. Just for fun if I have time I'll see if I can figure out very roughly what we would charge to do this.
So, Access. I would always say is a bad idea. I don't like it, at all...
However it does allow you to do a lot from a standing start (no need to set up database servers and all that). You do not need to know VBA to use it at all. You also do not really need to know SQL (but it helps to understand what's going on, if you have any spare time having a play with GalaxQL {web based, fun, SQL teaching tool} is always good.)
So you need to look at what your problem is and ask some questions:
Do you need to actually replace everything in one big go?
Could you perhaps streamline your process whilst replacing the system piecemeal?
The reporting is annual? Does everyone report at the same time, giving you a ton to do at once or does everyone report once per year around the year?
So, as a first step you could set up a webform of some sort which takes in all of the data and turns it into a CSV which you can import straight to the database. You can do this and test it and so on without impacting your workflow overly and then roll it out to a couple of your more friendly reporting services and see how they like it.
Or set up a new system of spreadsheets (excel spreadsheets are pretty handy really, everyone has excel, it's a familiar tool) which can have data validation on them, and then can all go into a touchdown folder to be auto processed from there?
Every step of they way look at your workflow and see where you can make small changes which won't break anything.
This way you don't need to heavily modify your database (which is the fragile and dangerous and expensive bit).
As to repair of the old database soleo gives good advice.
Make a copy, move it elsewhere, preferably a whole different computer (but that might be overkill) ditch the data in it and write yourself a bit of fake data that gives you known results.
Then you can start to modify that version of the database and run your fake data through it.
So long as you get the right answers you know you're on the right track. (Google: Regression Testing)
I'll ask the rest of the team over lunch and see if we come up with anything else...
posted by Just this guy, y'know at 3:33 AM on July 9, 2015 [4 favorites]
This is very much the sort of thing which we get hired to do, but of course we are very probably out of your budget. Just for fun if I have time I'll see if I can figure out very roughly what we would charge to do this.
So, Access. I would always say is a bad idea. I don't like it, at all...
However it does allow you to do a lot from a standing start (no need to set up database servers and all that). You do not need to know VBA to use it at all. You also do not really need to know SQL (but it helps to understand what's going on, if you have any spare time having a play with GalaxQL {web based, fun, SQL teaching tool} is always good.)
So you need to look at what your problem is and ask some questions:
Do you need to actually replace everything in one big go?
Could you perhaps streamline your process whilst replacing the system piecemeal?
The reporting is annual? Does everyone report at the same time, giving you a ton to do at once or does everyone report once per year around the year?
So, as a first step you could set up a webform of some sort which takes in all of the data and turns it into a CSV which you can import straight to the database. You can do this and test it and so on without impacting your workflow overly and then roll it out to a couple of your more friendly reporting services and see how they like it.
Or set up a new system of spreadsheets (excel spreadsheets are pretty handy really, everyone has excel, it's a familiar tool) which can have data validation on them, and then can all go into a touchdown folder to be auto processed from there?
Every step of they way look at your workflow and see where you can make small changes which won't break anything.
This way you don't need to heavily modify your database (which is the fragile and dangerous and expensive bit).
As to repair of the old database soleo gives good advice.
Make a copy, move it elsewhere, preferably a whole different computer (but that might be overkill) ditch the data in it and write yourself a bit of fake data that gives you known results.
Then you can start to modify that version of the database and run your fake data through it.
So long as you get the right answers you know you're on the right track. (Google: Regression Testing)
I'll ask the rest of the team over lunch and see if we come up with anything else...
posted by Just this guy, y'know at 3:33 AM on July 9, 2015 [4 favorites]
« Older New job, planned vacations and possibly needing to... | Help me find the right volunteering opportunity Newer »
This thread is closed to new comments.
Does each of your 450 services complete the same form? How many fields are we talking about, and how much does the form change from year to year ? This is not that difficult to put together using a decent CMS: Drupal for example has webform modules that you could set up without writing a single line of code.
Then there'd be a reporting tool that could generate the reports and let me redesign them without needing to faff around writing the whole thing directly in code. Is that possible on a tiny budget? Or is it just a pipe dream? And if it is possible, what software should we be looking at?
Have a look at JasperReports Server: they have a free, feature complete Community Edition that comes with a decent GUI for designing reports. Of course you'd need your data to be in something fancier than an Access database.
what are your database and reporting recommendations for somewhere that has no money and no in-house technical expertise?
Sorry to be disappointing, but this is not the kind of problem you solve well without either money or skills plus time.
If you remove the no money constraint, the standard answer would be to hire some consulting to analyze your processes and document all the cruft, then get some offers from software companies that would build you a system. It's the kind of project that tends to go overdue and over budget - mostly because of all the parts that are currently ticking along without anybody in the organization really knowing how.
posted by Dr Dracator at 8:24 AM on July 8, 2015 [1 favorite]