Search and combine fields in base or calc

Hi
I have a task to perform and would appreciate if someone could give some advice. I work with a large database (in LibreOffice Base) of animals captured in the wild and marked. They are marked with rings, collars or both, an can be recaptured many times. Each database entry is a capture event for a singe animal. The ring number and collar number are fields of the table, and a single individual animal can be represented in the table with a ring number only, then further along he can have both a ring and a collar and later on they might lose the ring or the collar. The task is to create a field as a combination of ring and collar number, but that will search the database for ring OR collar number and create an identification for the individual with a combination of both. The biggest problem is the individuals that were marked early on with a ring and later received also a collar and lost the ring afterwards. I need to find the entries that have non null values for both and copy the ring and collar numbers to the other entries where they are missing, so the individual can be properly identified. I would not mind doing this in calc in the exported data table if it is easier. I have been doing this manually, but with more than one thousand individual animals and many recaptures, it is becoming prohibitive. I really appreciate if someone can shed some light into the problem.

The problem sounds straightforward. For starters which database flavor are you connecting to? Since you didn’t put your user-id on this message there is a good chance you will never see this comment… Suggest you delete this question and re-ask it again w/ your real user name.

Hello,

You can update your fields with SQL Update statements. My SQL is a bit convoluted but it works. The problem is not knowing how your DB is actually structured. So as an example, having this table (named CAPTURES):

image description

This statement will update “RING” field in all records:

UPDATE "CAPTURES"
   SET "RING" = (SELECT MYRING
                 FROM (SELECT MYRING,  ID
                       FROM CAPTURES
                         LEFT JOIN (SELECT  "RING" MYRING,
                                           "COLLAR" MYCOLLAR
                                    FROM (SELECT DISTINCT "RING",
                                                 "COLLAR"
                                          FROM "CAPTURES"
                                          WHERE ("RING" IS NOT NULL)
                                          AND   ("COLLAR" IS NOT NULL))) A
                                ON CAPTURES.RING = A.MYRING
                                OR CAPTURES.COLLAR = A.MYCOLLAR) B
                 WHERE CAPTURES.ID = B.ID)

Similar statements can be used to update the COLLAR & COMBO (COLLAR + RING) fields.

The other thing I would like to bring up is the structure of your DB. From your description, it seems to be a single table with a record for each capture. Your entire problem would not exist if you had a main table for an animal (containing RING & COLLAR) and a second table linked back to the first which contained a record for each capture of the data. Using a form filter, you could select the animal based upon RING or COLLAR and when the animal record displayed from the selection, the linked records from the Captures sub table would display. Then you simply add a new capture record for the animal. Also there is no need for the combination field.

Thanks a lot for the advice and the code. I will give it a try and post the result here. I have considered that ideally I would have a table for the individuals linked to a table of captures. The problem is that when I started the database 4 years ago, I did not plan to use collars in conjunction with the rings, so this was not an issue in my mind.

I will consider this possibility off adding a table as well, because even if we have some extra work, it should be much tidier and in the long run make it easier to curate the data. The animals are bats and sometimes they chew on the rings and damage the numbers, and we end up with captures with mismatched rings and collars. It would be easier to correct and check these mistakes as we type the data, rather than after exporting for analysis, as it can take days. Thanks again for the advice.