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.

Installation

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.

Example

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