Thursday, December 16, 2021

Google Docs needs a single user DBMS like Microsoft Access. Single user, like a spreadsheet program.

I use a Chromebook (Pixelbook from Google) and free Google Docs, mostly its spreadsheet program. I create my spreadsheets on a Google server so I do not need to be concerned about backing up.

Occasionally, I use my Windows 10 Microsoft Surface laptop to run single user database management system (DBMS) Microsoft Access, which came out about 30 years ago. Its basics haven't changed much. In some ways it has regressed. It's ridiculous "ribbon" at the top, which replaces drop down menus, keeps getting more convoluted. What happened to "open tables" I think it was called? Simpler is almost always better.

Also, the Access programming team has not adapted the product for more varied screen resolutions. There are numerous anomalies, including the "ribbon" and "Navigation Pane" (Who knew it was called that?) present with font size that's really small at default resolutions in Windows 10. A solution: Settings "Ease of Access" "Make text bigger" at 170%. Hit the Apply button and Access is now more readable with table and query names in the Navigation Pane larger. The "ribbon" is also larger. Unfortunately, Access error messages are impervious to anything and still have tiny font size. Argh. And the record number info at the bottom gets squeezed vertically to become unreadable. Doesn't Microsoft check this stuff?

Criticism of Access tends to default to it not being multi-user and online. Duh. Would you expect that of a spreadsheet program like Google Sheets or Microsoft Excel?

So why do I use Microsoft Access? Because it supports a relational database structure and query by example. Say what?

Query by example let's you create an ad hoc query based on multiple tables that connect to each other through common keys. A table contains data and is a bit like a spreadsheet in which rows are called records and columns are fields. It can also have a key field, which can contain a unique value, like Social Security number. More on this below.

I use Access for baseball research, which I then present in my blog:

radicalbaseball.blogspot.com

The database I use provides a good example. It contains tables:

People: about 20,000 major league players going back into the 1800s.

Batting: annual data for each player connected to the player via an alphanumeric key field. 110,000 records.

Pitching: same as batting except for pitching data.

Teams: more specific data about the teams.

Franchises: example, the Brooklyn Dodgers moved to Los Angeles in 1958 but all time franchise records apply going back into the 1800s. Another example: the original Washington Senators moved to Minneapolis in 1961 and renamed the team the Minnesota Twins. Similarly, the St. Louis Browns moved in 1954 and became the Baltimore Orioles.

Back to query by example. You open a blank query and drag in an "image" of tables with their fields. Let's say from my example the People and Batting tables. You drag your cursor from the PlayerID field in People to the same in Batting. Now you've got a one to many relationship. You cannot do this fundamental DBMS "join" in a spreadsheet. Now you do all sorts of wonderful things with all that data. You can also take small chunks of it and copy/paste it into a spreadsheet and do stuff that's more manageable there.

I do not know of any other end user program that does anything this powerful so easily. Microsoft Access generates SQL.

SQL (Structured Query Language) is a standardized programming language that's used to manage relational databases and perform various operations on the data in them. ... SQL became the de facto standard programming language for relational databases after they emerged in the late 1970s and early 1980s.

The user of Access can view and even edit the SQL that his query generates but that user has no need to do that.

I tell people that they should learn to use a spreadsheet, that it's the closest thing to programming for non techie people without learning to program. It can be used at work. But if not at work, it can be used in their own lives, sort of like learning to be an electrician, plumber, carpenter but not doing that as a profession. Such skills always come in handy. Same with knowing spreadsheets.

Access databases go beyond spreadsheets. They are not for everyone. But for single user work that exceeds the capability of a spreadsheet program, you cannot beat Access.

But I'd like to get away from my Windows 10 Microsoft Surface laptop and the stagnation that Microsoft has let impact Access. I'd like to stick with Google Docs on my Chromebook. So, we need Google to build its better version of Microsoft Access. Please.

No comments: