Sqlite qry to create a kinship view
Sqlite qry to create a kinship view - either temp or perm. Can also use as part of CTE query.
Kinship Colors.sql
you can then color the people based off that view
--Update Colors from Kinship TABLE
UPDATE PersonTable
SET Color = kt.Color
FROM (Select * from Kinship) as kt
WHERE PersonTable.PersonID = kt.PersonID
Color code summary (Working on new CTE to Spouses Spouse, Siblings and such) but this covers most of it
SELECT
CASE
WHEN P.Color = 1 THEN '1 red'
WHEN P.Color = 2 THEN '2 Lime'
WHEN P.Color = 3 THEN '3 Blue'
WHEN P.Color = 4 THEN '4 Pink'
WHEN P.Color = 5 THEN '5 Yellow'
WHEN P.Color = 7 THEN '7 Silver'
WHEN P.Color = 8 THEN '8 Brown'
WHEN P.Color = 9 THEN '9 Green'
WHEN P.Color = 11 THEN '11 Purple'
WHEN P.Color = 12 THEN '21 brown'
WHEN P.Color = 14 THEN '22 gray'
WHEN P.Color = 15 THEN '15 Apricot'
WHEN P.Color = 16 THEN '16 Lemon'
WHEN P.Color = 18 THEN '18 Mint'
WHEN P.Color = 19 THEN '19 Azure'
WHEN P.Color = 21 THEN '21 Orange'
WHEN P.Color = 27 THEN '27 Slate'
WHEN P.Color = 0 THEN '00 Black'
END AS color_T,P.Color AS Color_N,
COUNT(P.Color) AS Num,CASE
WHEN P.Color = 1 THEN 'Related Close'
WHEN P.Color = 2 THEN 'Spouse of Cousin'
WHEN P.Color = 3 THEN 'spouses'
WHEN P.Color = 4 THEN 'Spouse of GrandParent'
WHEN P.Color = 5 THEN 'Cousins'
WHEN P.Color = 7 THEN 'Distant Relation'
WHEN P.Color = 9 THEN 'spouse of aunt/uncle'
WHEN P.Color = 15 THEN 'half aunt/uncle'
WHEN P.Color = 16 THEN 'half cousin'
WHEN P.Color = 18 THEN 'Spouse of half cousin'
WHEN P.Color = 21 THEN 'Aunts Uncles'
WHEN P.Color = 27 THEN 'Unknown Relation'
WHEN P.Color = 0 THEN 'unrelated'END AS type
Uploaded files: