VT Aquatic Invertebrates

I apologize for wasting time down this rabbit hole, but, it's winter and I'd rather sit at my computer than pretend I enjoy going outside. Eventually I had to experiment with a database application on a PC, having spent my professional career as an applications programmer in the IBM world. I've got almost 2,000 images taken over two years. Putting them into a database would entail going back through and looking closely and I am bound to learn something about my subject in the process. LibreOffice is free, and what I use as an alternative to Microsoft Office. Generally, it works pretty well, although the database component (called simply 'Base') is pretty flaky. Flaky like it has crashed on me at least 30 times in the 10 days or so I've spent setting this up. Also, quirky with its own unique learning curve. Luckily there are YouTubes that provide the usual range of good-bad instructions. Now, the dry details.

I set up a relational database that is normalized - meaning a single piece of information is stored only once, and referred to by pointer from the places that data is used. For example, 'Stream Name' is used in each Site record, but the Site records don't have the name all spelled out, but a pointer to a record in a 'Stream' table where the name is stored once. If I need to correct the spelling of the stream name, I do it once in the stream table, and all records that reference it are corrected.

The design of the database is centered around Observation records that include 'where' 'what' and 'when'. The 'where' is the Site, the 'what' is the organism down to either family or genus level, and the 'when' is just a date. The Observation record 'extras' include an optional 'Notes' field, and a pointer to a locally-stored picture. The tables of the database are tied to each other by key fields as shown in this schematic:
This is the data entry form for managing sites. I included Lat/Long so I could actually show my sites in mapping programs :
The Organism table includes basic taxonomy down to family level, which is as far as I know about in many cases, and which is why I did not include Genus in this table. I created a separate Genus table for families that I thought I could identify to Genus level, which gets included as an optional field of an observation. This is the Organism maintenance form :
This is the Observation entry form, where I link the site to the organism and possibly genus, add notes and a picture.


Update 1/07/25 - I am going to re-implement this using MS Access: Libreoffice is way too buggy to be reliable for the amount of work it will take to enter the data. Will post updates as I go.

Learning MS Access - it's way more stable and logically organized than LibreOffice (LO). For example, drop-down boxes in LO, from the little I know, are defined in forms; in Access they can be defined at the table level so one implementation is effective for all uses. I have removed the link to my LO test DB because it was not well done and not worth anybody studying. The current version of Access as promoted by Microsoft forces the use of 'cloud storage' which I don't want. I have ordered a copy of Office 2016 for about $50 which should work. Have been practicing on an older version of Office 2007. Very good series of Access training videos from here (no sponsor relationship to me)

Update re MS Office 2016 on Windows 11. It was tricky to install. My new Win 11 machine came with Microsoft 365 'sort of' installed - basically present but not usable without buying a subscription. The subscription was not the show stopper, it was the way cloud storage is forced on you. You have to uninstall Microsoft 365 before you load other versions of Office, and the uninstall needed a 'troubleshooter' found on the Microsoft website. Once I got 365 off my machine, the install of MS Office 2016 went smoothly. It works. Access is WAY better than LO Base.

My new implementation design is similar to the one on LO. There are 3 main data entry forms - one for site maintenance, one for building the taxonomy tree, and one for entering observations. Screenshots of those screens, below. I've entered the organisms shown on the Macroinvertebrates.Org website into the taxonomy database - there are 147 of them, mostly down to the level of genus. I can post links to the database if anybody shows interest.