The report facility in TNG is very useful for checking and tidying the database. Many people find it useful to store these reports for future use although generally these reports are not made Active as they shouldn't be seen by anyone except administrators. However they can still be run from the Admin Reports page by clicking the button marked with a tick.
It may be useful to give these a higher Rank than other reports (e.g. 10) so as to move them to the bottom of the report list. Some reports can change the database simply by running them: these could sensibly be given a higher number so that they aren't run accidentally.
These reports are only specified using SQL rather than using the report generator as many can only be given this way. So on the "Add New" page on Admin Reports they are entered under the heading "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:". Set the Report Name and Description from the details below, give it a Rank/Priority of 10 or more but don't make it Active. (Apart from anything else, these queries do not apply any privacy or living protection.) Note that you will have to change table names (after FROM) in queries if you do not use the default names (e.g. tng_people).
More complex maintenance reports have their own page in the wiki and many of these are listed in the See Also sections.
From TNG v11, the Administration section contains a selection of these reports, to be found under Miscellaneous >> Data Validation.
Since the living flag is not part of the GEDCOM standard it may be necessary to initially mark all living individuals. Also it's easy to forget when adding new individuals. These reports flag various possible mistakes.
Children of recent adults not marked as living
This looks for all people born after 1915 or children of people born after 1900 who are not marked as having died or been buried. It can be easily adapted to other dates.
SELECT distinct ch.personID, ch.gedcom, ch.firstname, ch.lastname, ch.birthdate FROM (tng_families fam, tng_children c, tng_people ch) left join tng_people dad on husband=dad.personID and dad.gedcom=fam.gedcom left join tng_people mum on wife=mum.personID and mum.gedcom=fam.gedcom WHERE fam.familyID=c.familyID and c.personID=ch.personID and fam.gedcom=c.gedcom and c.gedcom=ch.gedcom and !ch.living and ch.deathdate="" and ch.burialdate="" and ((mum.living or year(mum.birthdatetr)>=1900) or (dad.living or year(dad.birthdatetr)>=1900) or year(ch.birthdatetr)>=1915) order by ch.birthdatetr desc
Dead people marked as living
This flags any people marked as having died who still have the living flag set. (If you want them still to be restricted it's probably better to use the Private flag.)
SELECT personID,gedcom,firstname,lastname,birthdate,deathdate FROM tng_people WHERE deathdate!="" and living
Sex of individuals
Sex of person has been omitted
This is inevitable for some entries (mainly stillbirths where sex is not recorded) but it's easy to miss setting the status when adding new individuals.
SELECT personID, gedcom, firstname, lastname FROM tng_people WHERE sex!="M" and sex!="F"
Sex doesn't match the traditional marriage relationship
Since same sex marriages is different, TNG doesn't enforce a rule that the husband is male and wife female, so this report picks out these out as well as mistakes.
SELECT h.gedcom, h.personID,h.firstname,h.lastname,h.sex, w.personID,w.firstname,w.lastname,w.sex, marrtype FROM tng_families f left join tng_people h on husband=h.personID and f.gedcom=h.gedcom left join tng_people w on wife=w.personID and f.gedcom=w.gedcom WHERE (h.personID is not NULL and h.sex !='M') or (w.personID is not NULL and w.sex!='F') ORDER BY f.familyID
People have different views on whether photos of living people should be shown to the world. The "always on" box overrides the TNG defaults and some of these queries can be adapted to check whether your own policy is being followed.
Children with photos always on
Children's photos are a particular concern on the internet so this lists those now under 18 who are shown to the world.
SELECT p.personID,p.gedcom,firstname,lastname,birthdate FROM tng_people p join tng_medialinks lnk on p.personID=lnk.personID and p.gedcom=lnk.gedcom join tng_media m on lnk.mediaID=m.mediaID WHERE birthdatetr>current_date() - interval 18 year and alwayson
Young adults with photos always on
This checks those between 18 and 25. The test can be inverted by substituting "=0" at the end.
SELECT DISTINCT p.personID, p.gedcom, firstname, lastname, birthdate FROM tng_people p JOIN tng_medialinks lnk ON p.personID = lnk.personID AND p.gedcom = lnk.gedcom JOIN tng_media m ON lnk.mediaID = m.mediaID WHERE birthdatetr > '0000-00-00' AND birthdatetr < current_date( ) - INTERVAL 18 year AND birthdatetr > current_date( ) - INTERVAL 25 year AND living AND alwayson =1
People marked as natural child of more than one family
This may indicate that an adopted or similar flag has been omitted, but more often is produced by unresolved issues in the research process. It shows any child who has more than one natural father or mother.
SELECT a.personID,a.gedcom,p.firstname,p.lastname, a.familyID, b.familyID FROM tng_children a JOIN tng_children b ON a.personID = b.personID AND a.gedcom = b.gedcom join tng_people p on a.personID=p.personID and a.gedcom=p.gedcom WHERE a.familyID != b.familyID AND (((a.frel = "" OR a.frel = "birth") AND (b.frel = "" OR b.frel = "birth")) OR ((a.mrel = "" OR a.mrel = "birth") AND (b.mrel = "" OR b.mrel = "birth")))
There is only one parent link to a family structure and no other significant information in the record or children referring to it. These relics can occur when trying to add a parent or sibling. If you know a person was married but not who to, then add a (possibly uncertain) date or type to the record.
SELECT f.familyID,f.gedcom,husband,wife,marrdate,marrtype FROM tng_families f WHERE (husband="" or wife="") and marrdate="" and marrtype="" and NOT EXISTS (select * from tng_children c where f.familyID=c.familyID and f.gedcom=c.gedcom)
It can also happen where there is just one child and no parents.
SELECT f.familyID,f.gedcom,ch.personID, husband,wife,marrdate,marrtype FROM tng_children ch JOIN tng_families f on ch.familyID=f.familyID and ch.gedcom=f.gedcom WHERE husband="" and wife="" and marrdate="" and marrtype="" and NOT EXISTS (select * from tng_children c where f.familyID=c.familyID and f.gedcom=c.gedcom and c.personID!=ch.personID)
Ordering of children in families
TNG adds to the GEDCOM system an ordering on children within a family, which is particularly useful if dates of birth are not known for some or all children. This report prints out families in which the dates of birth of the children do not follow this ordering, together with a link to the father (or mother if no father).
SELECT distinct c.familyID,concat("<a href='getperson.php?personID=",p.personID,"&tree=",p.gedcom,"'>",p.firstname," ",p.lastname,"</a>") as parent FROM tng_children c join tng_children d on c.familyID=d.familyID and c.gedcom=d.gedcom join tng_people c1 on c.personID=c1.personID and c.gedcom=c1.gedcom join tng_people d1 on d.personID=d1.personID and d.gedcom=d1.gedcom join tng_families f on c.familyID=f.familyID and c.gedcom=f.gedcom join tng_people p on if(f.husband!="",f.husband=p.personID, f.wife=p.personID) and f.gedcom=p.gedcom WHERE c1.birthdatetr!=0 and d1.birthdatetr!=0 and c1.birthdatetr<d1.birthdatetr and c.ordernum>d.ordernum ORDER by c.familyID
Note that TNG rather oddly makes both "BEF 1900" and "AFT 1900" the same as 1900-00-00 so if these GEDCOM styles are used, some extra lines may be printed on this report.
Places that might be the same
This lists places which are the same up to the first comma, on one line. The numbers in brackets are the total number of instances of that name and the names are listed in decreasing order of possibilities and instances. No account is taken of different trees. You need then to use the Admin Places edit and merge tabs to make any corrections necessary.
select root, c, variants from (select substring_index(place, ",",1) root, count(*) c, sum(ct) tot, group_concat( place," (", ct,")" order by place separator " + ") variants from (select place,count(*) ct from (SELECT personID id, birthplace place, "b" t FROM tng_people WHERE birthplace !="" union SELECT personID,altbirthplace, "a" FROM tng_people WHERE altbirthplace !="" union SELECT personID,baptplace, "b" FROM tng_people WHERE baptplace !="" union SELECT personID,deathplace, "d" FROM tng_people WHERE deathplace !="" union SELECT personID,burialplace, "r" FROM tng_people WHERE burialplace !="" union SELECT personID,confplace, "c" FROM tng_people WHERE confplace !="" union SELECT personID,initplace, "i" FROM tng_people WHERE initplace !="" union SELECT personID,endlplace, "f" FROM tng_people WHERE endlplace !="" union SELECT eventID, eventplace, "e" FROM tng_events WHERE eventplace !="" union SELECT familyID, marrplace, "m" FROM tng_families WHERE marrplace !="" union SELECT familyID, divplace, "d" FROM tng_families WHERE divplace !="" union SELECT familyID, sealplace, "s" FROM tng_families WHERE sealplace !="" ) b group by place ) a group by root ) d where c>1 order by c desc, tot desc
Redundant places in the places list
TNG stores latitude and longitude and place levels in a separate table but this isn't pruned when mistakes are corrected. This report lists those places which have no corresponding entry in the tree. It may be best to merge entries first to avoid losing information.
select p.place, p.latitude, p.longitude from tng_places p left join (SELECT birthplace place FROM tng_people WHERE birthplace !="" union SELECT altbirthplace FROM tng_people WHERE altbirthplace !="" union SELECT baptplace FROM tng_people WHERE baptplace !="" union SELECT deathplace FROM tng_people WHERE deathplace !="" union SELECT burialplace FROM tng_people WHERE burialplace !="" union SELECT confplace FROM tng_people WHERE confplace !="" union SELECT initplace FROM tng_people WHERE initplace !="" union SELECT endlplace FROM tng_people WHERE endlplace !="" union SELECT eventplace FROM tng_events WHERE eventplace !="" union SELECT marrplace FROM tng_families WHERE marrplace !="" union SELECT divplace FROM tng_families WHERE divplace !="" union SELECT sealplace FROM tng_families WHERE sealplace !="" ) b on p.place=b.place where b.place is null order by p.place