MS Access or open-source database

[Clariman]

I have some data which I want to put in a format that is easy to analyse. It will be about 350 rows of data and about 32 attributes for each row. I could put it in a spreadsheet with filtering of the columns (32 of them) but it might be a bit clunky. Am wondering whether it is worth putting it into a database and having a simple table structure. Ideally I would like to be able to share the data online in some way - could be an online database or simply as a downloadable spreadsheet or database table.

What software would you suggest? MS Access doesn't seem to be easy to get hold of these days - at least not on a perpetual licenses basis. Are there good open-source databases that are widely used that also have a front-end to do the analysis?

Thanks
Clariman

[ReformedCharacter]

I have some data which I want to put in a format that is easy to analyse. It will be about 350 rows of data and about 32 attributes for each row. I could put it in a spreadsheet with filtering of the columns (32 of them) but it might be a bit clunky. Am wondering whether it is worth putting it into a database and having a simple table structure. Ideally I would like to be able to share the data online in some way - could be an online database or simply as a downloadable spreadsheet or database table.

What software would you suggest? MS Access doesn't seem to be easy to get hold of these days - at least not on a perpetual licenses basis. Are there good open-source databases that are widely used that also have a front-end to do the analysis?

Thanks
Clariman

I looked into this a few years ago (so may be a bit out of date). OpenOffice Base / LibreOffice Base may be an option if you are sure you want a database. Most databases (including Access) take some investment in time to learn. There are plenty of complicated free databases available but most will be far too sophisticated and difficult to use for your requirements. Unless you really want to go the database route, I'd suggest that you would be better off with a spreadsheet. Google Sheets are easy to share online.

RC

[stewamax]

Firstly,if you want to make the database available to others online - whether interactive or as a download - the recipient will also need some tool to display it. So just having single MSAccess licences may not do what your want.

Secondly, your database appears to be a single table and using a database management system for this is overkill unless you have huge amounts of data: an Excel spreadsheet for example has a limit of a million rows and 16K columns. And if you run Access entirely within a PC you are still limited: maximum table size is 2GB. You can of course run Access in split mode, with the 'storage' half living centrally and the analysis half living on a PC, but it is more usual to use SQL Server as the central back-end.

Thirdly, Base (in OpenOffice and in Libre Office - both free) is very similar to the older versions of Access except for the reporting functions which are completely different (or they were when I last used them). You can for example link to an Access database from LibreOffice, and the table display and record selection features are similar, but LibreOffice does not understand any Access reports.

If you record selections are complex, it is worth staying with Excel and looking at writing some Visual Basic to do the selection. Or use Google Sheets with Apps Script

[Slarti]

I have some data which I want to put in a format that is easy to analyse. It will be about 350 rows of data and about 32 attributes for each row. I could put it in a spreadsheet with filtering of the columns (32 of them) but it might be a bit clunky. Am wondering whether it is worth putting it into a database and having a simple table structure. Ideally I would like to be able to share the data online in some way - could be an online database or simply as a downloadable spreadsheet or database table.

What software would you suggest? MS Access doesn't seem to be easy to get hold of these days - at least not on a perpetual licenses basis. Are there good open-source databases that are widely used that also have a front-end to do the analysis?

Thanks
Clariman

I've done quite a lot of Access development and unless I wanted things locked down hard, for under 500 records, I wouldn't bother, I'd use Excel because of the distribution problems.

There is an Access addon that allows you to create an executable for distribution, but it is a lot of work to make it bulletproof.

Slarti

[XFool]

If it's simple you want, aeons ago we used this at work when it was a commercial program:

https://www.cardbox.com

[scotia]

I think a lot depends on how much work you want to put into learning to use a new tool, and also the amount of processing you would like to carry out. I have used Access professionally for many years. The language behind it is (VBA) Visual Basic for Applications - which you possibly already use with Excel Spreadsheets. The Microsoft suite - Access, Word and Excel all use VBA, and they can be easily interfaced with one another - e.g. using Access you can read and generate Excel and Word files. If your current task is a one-off, then maybe this approach is a bit over the top, but if you are thinking of developing databases with fairly complex data interactions, then it may be worth the effort.
A Runtime Version of Access is freely available - so if you want to distribute your database to other users, then they can use it with Runtime. I have tested Runtime with my software, and have experienced no problems, however my clients use the full versions of Access, so I have no extensive knowledge of any Runtime snags that I have not fallen over.
A major limitation of Access is that it runs only under Windows on PCs.

[jofc]

If you want to learn how to make a database & query it with SQL commands (rather than just do the job quickly with excel)

You could look at sqlite3
Its open source & even built in to android phones.
There are several free apps that give you a better user experience than just using the command line too.

Beyond that you get into a whole world of non SQL databases & concepts of big data that just do my head in....

Jof

[Clariman]

Thanks for your input. I can probably do what I need for my own research with a spreadsheet but I'll have no data normalisation so will end up with loads of redundant data e.g. re-typing county names rather than having them in a County table etc. However, that's not a big deal for 300 or so rows.

My main thinking was if I made the data available as a public resource for other researchers, then I'd like to have them able to search and query the data without downloading a copy of my spreadsheet. Indeed, I may want to retain copyright or other form of ownership over the data in the format which I will collect it.

C

[Slarti]

Thanks for your input. I can probably do what I need for my own research with a spreadsheet but I'll have no data normalisation so will end up with loads of redundant data e.g. re-typing county names rather than having them in a County table etc. However, that's not a big deal for 300 or so rows.

My main thinking was if I made the data available as a public resource for other researchers, then I'd like to have them able to search and query the data without downloading a copy of my spreadsheet. Indeed, I may want to retain copyright or other form of ownership over the data in the format which I will collect it.

C

If the county names are in the same column should should only need to type each one in full once as, if you need to retype Northumberland in a later row as you start to type northu it should then offer you Northumberland in full in the correct case. And if you don't have anything in Northamptonshire it will offer Northumberland as soon as you type n.

For your second point, print the spreadsheet to PDF and let them search that.

Slarti

[Itsallaguess]

If the county names are in the same column should should only need to type each one in full once as, if you need to retype Northumberland in a later row as you start to type northu it should then offer you Northumberland in full in the correct case.

And if you don't have anything in Northamptonshire it will offer Northumberland as soon as you type n.

Another option might be to create dynamic drop-down lists for those sections of the Excel data where it might be appropriate, such as counties in this example.

Some information here if anyone is interested -

In this article, we will learn how to create dynamic drop down list in Microsoft Excel.

As we know Data Validation feature improves the efficiency of data entry in excel and reduces mistakes and typing errors. It is used to restrict the user for the type of data that can be entered in the range. In case of any invalid entry, it shows a message and allows user to enter the data based on specified condition.

But a dynamic drop down list in Excel is a more convenient way of selecting data, without making any changes to the source. In other words, say you are going to update the list frequently which you’ve taken in drop down list. And, you are thinking if you make any changes in the list, you need to modify the data validation every time in order to get the updated drop down list.

But, this is where dynamic drop down comes into the picture, and it is the best option to select data without making any changes in the data validation. It is very similar to the normal data validation. However, when you update the list, the dynamic drop down list changes to accommodate that action, whereas the normal drop down list does not.

https://www.exceltip.com/data-tools/dyn ... -list.html

Cheers,

Itsallaguess