Census Needed – Named Group #namedgroup #census


Builds a group of persons whose lifetime probably spanned a user-defined Census Year and who had some event in the user-defined jurisdiction but not a Census fact for that year. The same script can be used for many named groups having different Census Years and target locations. The script includes examples of building lists of RINs that behave like RM’s manual marking and unmarking of persons for a group.


2021-11-25 adapted for #RM8

CensusNeededGroup.sql (for RM4 to pre-RM8)
– initial query works on individual facts only.

CensusNeededGroup2.sql (for RM4 to pre-RM8)
rev. 2020-05-16 to correct misuse of LabelID
– this query works on all types of facts, individual, family and shared by re-using the LifeLines query – a bulldozer solution for a shovel sized pile, but, what the heck!, it was all ready to use.


  1. Backup your database before you do this every time until you are confident that it does no harm.
  2. In RM, create a named group having the words “Census needed” and “#”CensusYear and Jurisdiction (in that order and without the quotes), where CensusYear is the year of the census, Jurisdiction is the country, or province/state, or municipality.
    e.g., “*Census needed #1871 Canada”
  3. Open your RM database with SQLite Expert Personal and load CensusNeededGroup.sql or CensusNeededGroup2.sql for RM4-preRM8 or, for RM8 databases, CensusNeededGroup2-RM8.sql into the SQL editor.
  4. Execute the query (F5)
  5. You will be prompted to enter a value for @CensusYear. In this example, enter “1871” (without quotes and without the leading #).
  6. You will be prompted to enter a value for @Jurisdiction. In this example, enter “Canada”.
  7. When the query has finished, you will need to refresh the RM4 sidebar view of your group by selecting another group and then re-selecting it.


The script looks for the group name that matches its search string. If it does not find a match, nothing happens. Otherwise, the current members list for the group is deleted and the script proceeds to build the new list. Each group has to have a unique name matching the rules above; the user of the script enters the variables year and jurisdiction for it to act on any one of the groups.

Persons having neither a Birth or Death date are ignored by the script as it cannot determine anything about their probability of being alive in the Census Year. Persons having both dates are readily evaluated against the Census Year while those having only one date are given a lifespan of 75 years – that number can be easily found in the script and changed. It could also be incorporated as a parameter specified at run time.

The @Jurisdiction parameter is used to search events with a place name containing the search string. This could be as broad as a country “Canada” as in the example; a state “, OH,” if you used abbreviations (and the leading and trailing punctuation would be needed to avoid picking up every place with “oh” in the name), or a town. If any event occurred in a matching place, that person will be selected unless they have a Census fact for that year.

Thus it is important that there be at least an estimated Birth date and/or an estimated Death date and a guess at the Place of one of these or any other individual event.

To add selected persons that are to be included or excluded from the group, look to the last two sql statements (commented out) in the CensusNeededGroup.sql script. They show how to build the lists and actually carry out the marking and unmarking of the same RINs (net change = 0).

One Reply to “Census Needed – Named Group #namedgroup #census

  1. Updated for RM8. Executed the building of a 1700 member group in the province of Ontario, Canada needing the 1921 Census from a database of 195,000 people in 49 seconds. The RM8.1.2 Who Was There Report was not finished after 25 minutes but, based on prior runs, is projected to take on the order of 100 minutes!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.