Query to count ancestors using a given Name
Posted: 16 Nov 2002 20:45
Custom query to count ancestors using a given Christian name
Date: Tue, 16 Jul 2002 12:24:22 +0100
I said a while ago that I might send out the odd tip, when things got a bit quiet. Here's another.
Douglas Maydew sent me an email asking how to put together a query that will give you a count of all of your ancestors who use a given Christian name. The starting point for this is Chapter 12 of the User Manual, but its a bit skimpy so I thought that another example wouldn't hurt.
(1) Follow the instructions on p.104 of the User Manual, to create a new Custom Query (type= Individuals). Give it whatever name you like.
(2) Click on the Columns tab. You should already have a column with heading of 'Individual' and Expression 'INDI'. In the Field lists on the left-hand-side, expand the Name field and select 'Given'. Take note of the box below the Fields list. It says the following 'The 'given name' is the always first name, unless a 'given name used' value has been provided, in which case that is the given name, or unless the first name is the surname, in which case the given name is the first non-surname word in the name.'
The point of the 'Given Named Used' field (available from the Details tab of the Property Dialog) is that many people spend their lives being called by a name that is one of their given names, but not the first. In the example in the manual, there is 'Jean Clare Anderson', always known as 'Jean Clare'. Her 'First' name (as far as F.H. is concerned at least) is 'Jean'. For someone like that, you should set 'Given Name Used' to 'Jean Clare'. Again, if 'Angus David Jones' was always known as 'David', you should set 'Given Name Used' to 'David'. If 'Angus David Jones' was always known as 'Angus', you don't have to (and you shouldn't) set 'Given Name Used' to 'Angus'. This is unnecessary (and too much hard work). F.H. always assumes that the first name (or rather, the first non-surname name, because in some cultures the surname comes first) is the given name that was actually used, if 'Given Name Used' is not supplied.
Click on the '>' button below the field list to add ':GIVEN' to the columns list.
(3) Select the new column that has been added (click on 'Name(GIVEN)' in the leftmost column of the Columns list). In the Heading field (below the Columns list) change 'Name(GIVEN)' to 'Given Name Used' and click on the 'Update' button.
(4) Now select the Rows tab. Click on the Relations tab at the bottom. The Condition should be 'Add if..', the Relationship should be 'Ancestor', leave '(max generations)' blank. Set 'Of' to 'This Individual' (but leave the grey field next to it blank). Ensure that 'Parameter' is checked. Set 'Label' to 'Starting Person'. Click 'Add'.
(5) Still in the Rows tab, click on the General tab at the bottom. Set the Condition to 'Exclude Unless'. With the Expression field click on the down arrow at righthand end of the field. You should see 'INDI.NAME[1]:GIVEN' in the list. Select that. Set the Operator to 'matches'. Leave the Value field blank, and click on 'Parameter'. Set the 'Label' field to 'Given Name Used'. Click on the 'Add' button.
Now run the query. A dialog box comes up asking you for a 'Starting Person' and for a 'Given Name Used'. Specify the person whose ancestors you want to check as the Starting Person. Specify the Christian name you are interested in as the 'Given Name Used' (e.g. 'Thomas'). Press OK. If you used Thomas, you should see a list of people (possibly none, depending on your data, of course), whose names started with Thomas or who always used Thomas as their given name. The Given Name Used field should always just have Thomas in it, in each row. In the status bar pane, right at the bottom of the window, you should see 'Records in Result Set:' followed by a number. This will tell you how many ancestors used Thomas (or the name you specified) as their given name.
Obviously you could vary this is many ways. For example, if you want to find anyone who has the Christian name anywhere in their name (including their surname), do the following: Create another custom query, as before, but this time skip steps (2) and (3) altogether (so you only have one column: Individual). In Step (5), when selecting an expression for the Expression field, instead of clicking on the down arrow to select INDI.NAME[1]:GIVEN, click on the button with 3 dots to the right of the Expression field, and select 'Name' (unexpanded) from the list that appears. This will give an unqualified name expression which looks like this: INDI.NAME[1]. Press OK. Set the Operator field to 'Contains' rather than 'Matches', and use a label of 'Name' where previously you had used 'Given Name Used'; but otherwise, do everything as before. When you run this query, it will find any ancestors of the Starting Person, who have the Name you specify, somewhere as part of their name.
It sounds more complicated than it is.
Simon Orde List Administrator and Family Historian designer
Date: Tue, 16 Jul 2002 12:24:22 +0100
I said a while ago that I might send out the odd tip, when things got a bit quiet. Here's another.
Douglas Maydew sent me an email asking how to put together a query that will give you a count of all of your ancestors who use a given Christian name. The starting point for this is Chapter 12 of the User Manual, but its a bit skimpy so I thought that another example wouldn't hurt.
(1) Follow the instructions on p.104 of the User Manual, to create a new Custom Query (type= Individuals). Give it whatever name you like.
(2) Click on the Columns tab. You should already have a column with heading of 'Individual' and Expression 'INDI'. In the Field lists on the left-hand-side, expand the Name field and select 'Given'. Take note of the box below the Fields list. It says the following 'The 'given name' is the always first name, unless a 'given name used' value has been provided, in which case that is the given name, or unless the first name is the surname, in which case the given name is the first non-surname word in the name.'
The point of the 'Given Named Used' field (available from the Details tab of the Property Dialog) is that many people spend their lives being called by a name that is one of their given names, but not the first. In the example in the manual, there is 'Jean Clare Anderson', always known as 'Jean Clare'. Her 'First' name (as far as F.H. is concerned at least) is 'Jean'. For someone like that, you should set 'Given Name Used' to 'Jean Clare'. Again, if 'Angus David Jones' was always known as 'David', you should set 'Given Name Used' to 'David'. If 'Angus David Jones' was always known as 'Angus', you don't have to (and you shouldn't) set 'Given Name Used' to 'Angus'. This is unnecessary (and too much hard work). F.H. always assumes that the first name (or rather, the first non-surname name, because in some cultures the surname comes first) is the given name that was actually used, if 'Given Name Used' is not supplied.
Click on the '>' button below the field list to add ':GIVEN' to the columns list.
(3) Select the new column that has been added (click on 'Name(GIVEN)' in the leftmost column of the Columns list). In the Heading field (below the Columns list) change 'Name(GIVEN)' to 'Given Name Used' and click on the 'Update' button.
(4) Now select the Rows tab. Click on the Relations tab at the bottom. The Condition should be 'Add if..', the Relationship should be 'Ancestor', leave '(max generations)' blank. Set 'Of' to 'This Individual' (but leave the grey field next to it blank). Ensure that 'Parameter' is checked. Set 'Label' to 'Starting Person'. Click 'Add'.
(5) Still in the Rows tab, click on the General tab at the bottom. Set the Condition to 'Exclude Unless'. With the Expression field click on the down arrow at righthand end of the field. You should see 'INDI.NAME[1]:GIVEN' in the list. Select that. Set the Operator to 'matches'. Leave the Value field blank, and click on 'Parameter'. Set the 'Label' field to 'Given Name Used'. Click on the 'Add' button.
Now run the query. A dialog box comes up asking you for a 'Starting Person' and for a 'Given Name Used'. Specify the person whose ancestors you want to check as the Starting Person. Specify the Christian name you are interested in as the 'Given Name Used' (e.g. 'Thomas'). Press OK. If you used Thomas, you should see a list of people (possibly none, depending on your data, of course), whose names started with Thomas or who always used Thomas as their given name. The Given Name Used field should always just have Thomas in it, in each row. In the status bar pane, right at the bottom of the window, you should see 'Records in Result Set:' followed by a number. This will tell you how many ancestors used Thomas (or the name you specified) as their given name.
Obviously you could vary this is many ways. For example, if you want to find anyone who has the Christian name anywhere in their name (including their surname), do the following: Create another custom query, as before, but this time skip steps (2) and (3) altogether (so you only have one column: Individual). In Step (5), when selecting an expression for the Expression field, instead of clicking on the down arrow to select INDI.NAME[1]:GIVEN, click on the button with 3 dots to the right of the Expression field, and select 'Name' (unexpanded) from the list that appears. This will give an unqualified name expression which looks like this: INDI.NAME[1]. Press OK. Set the Operator field to 'Contains' rather than 'Matches', and use a label of 'Name' where previously you had used 'Given Name Used'; but otherwise, do everything as before. When you run this query, it will find any ancestors of the Starting Person, who have the Name you specify, somewhere as part of their name.
It sounds more complicated than it is.
Simon Orde List Administrator and Family Historian designer