Report: Census summaries for families

From TNG_Wiki
Jump to: navigation, search

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