Report: Census summaries for families
This report lists the contents of censuses for named individuals and families in time order. It should be quite easy to adjust the layout to suit your particular requirements.
Go to the Reports section on the Administration page and click on the Add New tab. Fill in the Report name and remember to click on the Active button at some stage. Scroll to the bottom where it says "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and copy and paste the Mysql code below, changing the tree and family names to suit.
SELECT eventdate,firstname,lastname, birthdate, eventplace,address1, info FROM tng_people a, tng_families b, tng_events c left join tng_addresses d on c.addressID=d.addressID and c.gedcom=d.gedcom WHERE a.gedcom='tree1' and a.gedcom=b.gedcom and b.gedcom=c.gedcom and (lastname='Norman' or lastname='Underwood') and (a.personID=b.husband and c.persfamID=b.familyID and c.eventtypeID=36 or c.persfamID=a.personID and c.eventtypeID=24 and b.familyID='F1') order by eventdate,lastname,firstname
Remember to change the items in single quotes (except F1). Then hit Save and Exit and click on the tick button next to the report name to test the result.
Note that this assumes the default naming of tables in TNG. If yours is different, then you will need to adjust the table names in the "FROM" part.
This shows the first few years of a typical report. The summaries are made by hand.
|eventdate||First Name||Last Name||Birth Date||eventplace||address1||Info|
|1841||Thomas||Norman||1812||All Saints, Leicester||Free School Lane,||Thomas Norman (29, plumber), Mary (36), Mary Underwood (9) + 1 apprentice, 1 servant|
|1841||William||Norman||1804||Leicester, Leicestershire||Vauxhall Street, All Saints||Will Norman (55*, carpenter, b Leics), Sarah (45, do), Henry (10), Naomi (5) + Tom, Mary, Caroline Walker|
|1851||Thomas||Norman||1812||All Saints, Leicester||12 Freeschool Lane,||Thomas Norman (38, Plumber & C Employs 4 Men & 1 Boy, b Newtown Linford, Leicester), Mary (47, b Gadsby Lodge), Mary Underwood (18, niece, do), John Underwood (17, apprentice, do) + 1 servant|
|1851||William||Norman||1804||Leicester, Leicestershire||Vauxhall Street, All Saints||William Norman (47 carpenter, b Sewton Linford) Elizabeth (45), Henry (22, plumber, b Nailstone)|
|1861||Henry||Norman||Abt 1830||Leicester, Leicestershire||59, Freeschool Lane, All Saints||Henry Norman (31, plumber & glazier, b Nailstone), Sarah (34, b Ringstead, Northamptonshire), John Underwood (27, plumber & glazer, b Gaddesby), Henry Underwood (19, do, do), George Elton (14, do, b Lutterworth)|
|1861||Thomas||Norman||1812||All Saints, Leicester||71, High Cross Street||Thomas Norman (58, Wood Plumber & Glazier), Mary (58), Ann Underwood (26, niece housekeeper), Elovia Underwood (15, niece servant) + 1 servant|
|1861||William||Norman||1804||Leicester, Leicestershire||3, Vauxhall Street, All Saints, Leicester||William Norman (56, carpenter joiner, b Northampton), Elizabeth (56, b Leicester)|
|1871||Henry||Norman||Abt 1830||Leicester, Leicestershire||11 Free School Lane, All Saints||Henry Norman (41, plumber master, ), Sarah (44), Charlotte Harridge (7, niece) + 1 servant, 1 apprentice|
|1871||Thomas||Norman||1812||All Saints, Leicester||15 Highcross Street,||Thomas Norman (58, hat maker, b Newton Linford), Mary (68, b Gaddesby), Mary Underwood (38, niece, housekeeper, b do) + 2 servants|
|1871||John||Underwood||4 Sep 1833||All Saints, Leicester||15 Free School Lane,||John Underwood (37, master plumber employing 14 men and 3 boys, b Gaddesby, Leics), Sarah (35m b Barsby), John (9), William H (6), Mary E (4), Alfred T (3) + 1 servant|
Customising the report
It's easy to adapt the report to produce more nearly the result you want. For example:
- The example above actually represents only John Underwood's censuses together with the Normans, produced by replacing the bracketted condition by
(lastname='Norman' or lastname='Underwood' and firstname='John').
- You can easily make similar adaptations. Note that 'and' binds more tightly than 'or'.
- You can vary the number of families by adjusting the same bracketted condition.
- The ordering of the report can be changed by interchanging the order of the fields in the last 'order by' line.
- The condition "and b.familyID='F1'" is simply there to suppress a repetition in the case that the individual census entries are used rather than families. If your database doesn't contain an F1, then any other valid family name will do.
- You may not use the "address1" line in the "More" section of the census event. In this case the SQL can be simplified to:
SELECT eventdate,firstname,lastname, birthdate, eventplace,info
FROM tng_people a, tng_families b, tng_events c
WHERE a.gedcom='tree1' and a.gedcom=b.gedcom and b.gedcom=c.gedcom and (lastname='Norman' or lastname='Underwood') and (a.personID=b.husband and c.persfamID=b.familyID and c.eventtypeID=36 or c.persfamID=a.personID and c.eventtypeID=24)
order by eventdate,lastname,firstname
- Note that 24 is the event type ID for individual census entries and 36 is that used for family census entries in the currently distributed version of TNG. If you don't get the results you expect, check the eventtypes table in your database and adjust these numbers if necessary. (They aren't distinguished in the table.)
- You may provide a link to the person's individual entry by replacing "firstname, lastname" in the first line of the code by:
concat("<a href='getperson.php?personID=",personID,"&tree=tree1'>",firstname," ",lastname,"</a>") AS name
For any further discussion, contact Chris Moss