Quote from Chad Kurszewski on 2022-02-06, 8:34 pm

I was interested in generating a list of living people in order to cross check when looking at recent obituaries in the area where much of my family lives.  I also wanted the list to have some additional details like birth date/place and their spouse's name (super useful for married women).  RM8 has a good advanced search feature, but unfortunately you cannot export that to excel or print it.

I am sub-novice for SQL, but with a bit of work, I was able to put this together.  I'm sure you experts could have done this much more elegantly, but I guess this is functional albeit crude.

Columns include their name, ID, their gender (limited to M/F), if they are related to you (if you have Set Relationships), Birth Date, Birth Place, and their spouse's surname/given name (first one only I believe, not all of them or the most recent).

I hope someone else finds this useful.  Please don't shred me for bad coding.

-- generate list of living people with additional info
SELECT a.Surname, a.Given, a.OwnerID, IIF(Sex,'F','M') AS Sex, IIF(Relate1>0,'Y','N') AS Related
,(select (SUBSTR(b.Date,4,4)||'-'||SUBSTR(b.Date,8,2)||'-'||SUBSTR(b.Date,10,2)) FROM eventtable b WHERE b.OwnerID = a.OwnerID AND b.eventType = 1) as Birth
, (select (select name from PlaceTable c where c.PlaceID=b.PlaceID AND b.OwnerID = a.OwnerID AND b.eventType = 1) FROM eventtable b WHERE b.OwnerID = a.OwnerID AND b.eventType = 1) AS BirthPlace
, (SELECT (SELECT Surname FROM NameTable c WHERE c.OwnerID=IIF(Sex,d.FatherID,d.MotherID) ) FROM FamilyTable d WHERE d.FamilyID=SpouseID) AS SpouseSurname
, (SELECT (SELECT Given FROM NameTable c WHERE c.OwnerID=IIF(Sex,d.FatherID,d.MotherID) )FROM FamilyTable d WHERE d.FamilyID=SpouseID) AS SpouseGiven
FROM NameTable a
INNER JOIN PersonTable ON PersonTable.PersonID = a.OwnerID
WHERE
Living = 1
AND IsPrimary = 1
AND a.Surname NOT LIKE '' and a.Surname NOT LIKE 'Unknown'
ORDER BY Surname, Given
;