* Query for deaths as a result of Childbirth
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Query for deaths as a result of Childbirth
When constructing my tree for the descendants of the Electress Sophia I was struck by how often I came across cases where mothers had died soon after childbirth
Im now trying to construct a query that might list all those. But I am stuck.
I started with the all individuals query. removed most of the colums and just left the Individuals name column.
I then added columns for their Birth date, Mothers Name, and mothers date of death.
I then excluded rows where the birth date was null and similarly rows where the mothers death date is null.
so I now have a list of all those records where i have a birth date and a mothers death date.
I now want to compare the dates and look at only those whose mothers died say within 3 months of the child being born.
I couldnt see a way of comparing column entries in FH so i exported the results to excel.
I then hit two snags
I knew Excel recorded dates as a figure and that by subtracting the death date from the birth date , i would be left with the number of days that were in between.
However some of my dates only show the year so i need to get rid of those which i think should be fairly easy but the second problem is that excel cant do any arithmetic on days before 1900. As far as its concerned 1 jan 1900 is day 1
So is FH any smarter can I compare dates and produce the sort of list I am looking for?
any help much appreciated as always
ID:6380
Im now trying to construct a query that might list all those. But I am stuck.
I started with the all individuals query. removed most of the colums and just left the Individuals name column.
I then added columns for their Birth date, Mothers Name, and mothers date of death.
I then excluded rows where the birth date was null and similarly rows where the mothers death date is null.
so I now have a list of all those records where i have a birth date and a mothers death date.
I now want to compare the dates and look at only those whose mothers died say within 3 months of the child being born.
I couldnt see a way of comparing column entries in FH so i exported the results to excel.
I then hit two snags
I knew Excel recorded dates as a figure and that by subtracting the death date from the birth date , i would be left with the number of days that were in between.
However some of my dates only show the year so i need to get rid of those which i think should be fairly easy but the second problem is that excel cant do any arithmetic on days before 1900. As far as its concerned 1 jan 1900 is day 1
So is FH any smarter can I compare dates and produce the sort of list I am looking for?
any help much appreciated as always
ID:6380
- Jane
- Site Admin
- Posts: 8518
- Joined: 01 Nov 2002 15:00
- Family Historian: V7
- Location: Somerset, England
- Contact:
Query for deaths as a result of Childbirth
You can do it in FH, you need to use an expression to add 3 months to the child's date and compare it to the Mothers date
You need to use the CalcDate and Dateif functions.
I don't have time to work it out tonight, but I am sure someone will be along to help if you get stuck while I am away.
You need to use the CalcDate and Dateif functions.
I don't have time to work it out tonight, but I am sure someone will be along to help if you get stuck while I am away.
-
- Famous
- Posts: 170
- Joined: 12 Sep 2003 14:56
- Family Historian: V6.2
- Location: Ogwell, Devon
- Contact:
Query for deaths as a result of Childbirth
Had a quick play and i think this is what you want
This gives
With these Columns defined
Although you can see that this has shown that i have some incorrect data as i seem to have dates wher people were born after their Mother had died.
This gives
With these Columns defined
Although you can see that this has shown that i have some incorrect data as i seem to have dates wher people were born after their Mother had died.
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Query for deaths as a result of Childbirth
Thanks for that, looks like that will do the trick.
However I have tried to set up that expression and dont seem to be able to do it properly.
Im in the rows screen and have the General tab selected.
for condition I have selected Exclude if from the drop down list.
I then typed in the Expression field 'timespan[%INDI.FAMC>WIFE>DEAT.DATE%%INDI.BIRT.DATE%MONTHS is less than 3
I then tried to add the expression but the add button is greyed out
The operator field has nothing in the drop down box and the Value Field is also greyed out.
Where am I going wrong.
However I have tried to set up that expression and dont seem to be able to do it properly.
Im in the rows screen and have the General tab selected.
for condition I have selected Exclude if from the drop down list.
I then typed in the Expression field 'timespan[%INDI.FAMC>WIFE>DEAT.DATE%%INDI.BIRT.DATE%MONTHS is less than 3
I then tried to add the expression but the add button is greyed out
The operator field has nothing in the drop down box and the Value Field is also greyed out.
Where am I going wrong.
- RogerF
- Famous
- Posts: 182
- Joined: 26 Apr 2009 16:32
- Family Historian: V6.2
- Location: Oxfordshire, England
- Contact:
Query for deaths as a result of Childbirth
Tim's expression starts =Timespan and ends -3. That may perhaps help.
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Query for deaths as a result of Childbirth
Tried putting in the = sign and -3 and changed the brackets to ( instead of [ , so the expression now reads =timespan(%INDI.FAMC>WIFE>DEAT.DATE%%INDI.BIRT.DATE%MONTHS) is less than -3
I still cant add the expression or do anything with the operator (drop down menu is blank) or value fields Field is greyed out)
I still cant add the expression or do anything with the operator (drop down menu is blank) or value fields Field is greyed out)
- tatewise
- Megastar
- Posts: 28449
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Query for deaths as a result of Childbirth
The Row Filter entry should be:
Condition: Exclude if
Expression: =Timespan(%INDI.FAMC>WIFE>DEAT.DATE%%INDI.BIRT.DATE%MONTHS)
Operator: is less than
Value: -3
As you are starting to learn, functions start with = and have parameters enclosed in parentheses ( ) and separated by commas.
The is less than is one of the possible operators, and -3 is the Value.
Do NOT put is less than -3 at the end of the Expression.
Condition: Exclude if
Expression: =Timespan(%INDI.FAMC>WIFE>DEAT.DATE%%INDI.BIRT.DATE%MONTHS)
Operator: is less than
Value: -3
As you are starting to learn, functions start with = and have parameters enclosed in parentheses ( ) and separated by commas.
The is less than is one of the possible operators, and -3 is the Value.
Do NOT put is less than -3 at the end of the Expression.
-
- Famous
- Posts: 170
- Joined: 12 Sep 2003 14:56
- Family Historian: V6.2
- Location: Ogwell, Devon
- Contact:
Query for deaths as a result of Childbirth
TateWise said
Expression: =Timespan(%INDI.FAMC>WIFE>DEAT.DATE%,%INDI.BIRT.DATE%,MONTHS)
Also if you were to swap Mothers Death Date and Individuals Birth Date round in the expression then you would need to change the operator to be greater than and value to be 3.
Depends on how you want query to read all though both the same.
(i.e. : Exclude if Birth Date more than 3 months before Death Date
or Exclude if Death Date more than 3 months after Birth Date)
Correct except he forgot the commas should actually beExpression: =Timespan(%INDI.FAMC>WIFE>DEAT.DATE%%INDI.BIRT.DATE%MONTHS)
Expression: =Timespan(%INDI.FAMC>WIFE>DEAT.DATE%,%INDI.BIRT.DATE%,MONTHS)
Also if you were to swap Mothers Death Date and Individuals Birth Date round in the expression then you would need to change the operator to be greater than and value to be 3.
Depends on how you want query to read all though both the same.
(i.e. : Exclude if Birth Date more than 3 months before Death Date
or Exclude if Death Date more than 3 months after Birth Date)
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Query for deaths as a result of Childbirth
Hmm really dont know what is wrong
Ive tried copying The esxpression =Timespan(%INDI.FAMC>WIFE>DEAT.DATE%,%INDI.BIRT.DATE%,MONTHS) and then pasting into the exprssion field as shown above but I cant then chose is greater than in the operator box. When i click on the arrow it is just all blanks.
Coud it have anything to do with the fact that i have'nt got a timespann column as shown in the example above.
also noticing that on the colums page none of my expressions habe a % sign in front
Ive tried copying The esxpression =Timespan(%INDI.FAMC>WIFE>DEAT.DATE%,%INDI.BIRT.DATE%,MONTHS) and then pasting into the exprssion field as shown above but I cant then chose is greater than in the operator box. When i click on the arrow it is just all blanks.
Coud it have anything to do with the fact that i have'nt got a timespann column as shown in the example above.
also noticing that on the colums page none of my expressions habe a % sign in front
-
- Famous
- Posts: 170
- Joined: 12 Sep 2003 14:56
- Family Historian: V6.2
- Location: Ogwell, Devon
- Contact:
Query for deaths as a result of Childbirth
Try
=Timespan(%INDI.~MOTH>DEAT.DATE%,%INDI.BIRT.DATE%,MONTHS) as the expression.
As the expression you are attempting to use is incorrect.
=Timespan(%INDI.~MOTH>DEAT.DATE%,%INDI.BIRT.DATE%,MONTHS) as the expression.
As the expression you are attempting to use is incorrect.
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Query for deaths as a result of Childbirth
Think i need to go back to the beggining as something is not right and take it bit by bit
Im going to start with the All individulas Standard query save it as a custom query called 'test' and delete the columns for Record ID Dates Birth Place and updated.
As expected that runs fine and gives me a list of 18007 names...which is what I expected
Now adding columns for Birth date.I need 3 , one for the day, one for the month , and one for the year
So clicking under individual, Events, Birth Date I find :DAY, :MONTH_NUMBER and :YEAR
If I run that I get a list of everyone with their day month and year of birth (Still 18007 records as expceted
So far so good All the expressions in the Columns page start with INDI (no % in front)
I now want to list only those for which i have a complete date of birth.
On the rows page I Exclude if year of death is null, and similarly day of death anfd month of death is null. Thats reduced it to 11815 records
Ok I now want to see if i have recorde their mothers so i ad a column for their mothers name and similarly 3 more columns for their Death dates
I am using the short cut to mother option and adding Mothers Name, Death,Date,Day Month number and year
That works ok buyt of course I have people listed without mothers and some with mothers who have no death date
Similar to above I exlude if Mothers birth day, month or year is null.
That works nicely and i now have a list of 3589 names of individuals with the date their mother died.
I will now try and add a column to show me the difference between the year the mother died and the year the child died
I have tried putting a heading of Difference
Then the Expression
=Timespan(%INDI.~MOTH>DEAT.DATE:YEAR%,%INDI.BIRT.DATE:YEAR%)
and also
=Timespan(%INDI.~MOTH>DEAT.DATE:YEAR%,%INDI.BIRT.DATE:YEAR%,MONTHS)
But each time I try to add I am told the expression is wrong.
I thought the SYNTAX for the timespan Expression was
=Timespan(%FIRSTVARIABLE%,%SECONDVARIABLE%) with the option to add a value before the final bracket such as ,MONTHS.
I am using the Normal Brackets above the 9 and O on the keyboard.
So where am I going wrong.
Im going to start with the All individulas Standard query save it as a custom query called 'test' and delete the columns for Record ID Dates Birth Place and updated.
As expected that runs fine and gives me a list of 18007 names...which is what I expected
Now adding columns for Birth date.I need 3 , one for the day, one for the month , and one for the year
So clicking under individual, Events, Birth Date I find :DAY, :MONTH_NUMBER and :YEAR
If I run that I get a list of everyone with their day month and year of birth (Still 18007 records as expceted
So far so good All the expressions in the Columns page start with INDI (no % in front)
I now want to list only those for which i have a complete date of birth.
On the rows page I Exclude if year of death is null, and similarly day of death anfd month of death is null. Thats reduced it to 11815 records
Ok I now want to see if i have recorde their mothers so i ad a column for their mothers name and similarly 3 more columns for their Death dates
I am using the short cut to mother option and adding Mothers Name, Death,Date,Day Month number and year
That works ok buyt of course I have people listed without mothers and some with mothers who have no death date
Similar to above I exlude if Mothers birth day, month or year is null.
That works nicely and i now have a list of 3589 names of individuals with the date their mother died.
I will now try and add a column to show me the difference between the year the mother died and the year the child died
I have tried putting a heading of Difference
Then the Expression
=Timespan(%INDI.~MOTH>DEAT.DATE:YEAR%,%INDI.BIRT.DATE:YEAR%)
and also
=Timespan(%INDI.~MOTH>DEAT.DATE:YEAR%,%INDI.BIRT.DATE:YEAR%,MONTHS)
But each time I try to add I am told the expression is wrong.
I thought the SYNTAX for the timespan Expression was
=Timespan(%FIRSTVARIABLE%,%SECONDVARIABLE%) with the option to add a value before the final bracket such as ,MONTHS.
I am using the Normal Brackets above the 9 and O on the keyboard.
So where am I going wrong.
-
- Famous
- Posts: 170
- Joined: 12 Sep 2003 14:56
- Family Historian: V6.2
- Location: Ogwell, Devon
- Contact:
Query for deaths as a result of Childbirth
You are using a Year qualifier in the Expression, when it should be just the date.
I.e.
You have
=Timespan(%INDI.~MOTH>DEAT.DATE:YEAR%,%INDI.BIRT.DATE:YEAR%,MONTHS)
but should be
=Timespan(%INDI.~MOTH>DEAT.DATE%,%INDI.BIRT.DATE%,MONTHS)
I.e.
You have
=Timespan(%INDI.~MOTH>DEAT.DATE:YEAR%,%INDI.BIRT.DATE:YEAR%,MONTHS)
but should be
=Timespan(%INDI.~MOTH>DEAT.DATE%,%INDI.BIRT.DATE%,MONTHS)
- tatewise
- Megastar
- Posts: 28449
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Query for deaths as a result of Childbirth
When reviewing the SYNTAX of a FUNCTION take a look at the FH Help > Family Historian Help + Using Family Historian + Advanced Topics + Understanding Functions + Functions (all) and open the page for the FUNCTION where it explains the parameters and syntax, and gives an example.
In many cases, some parameters are DATA REFERENCES, which has a help page nearby called Understanding Data References.
Also see the Knowledge Base page Creating a Query that has links to Knowledge Base pages on Understanding Data References and Understanding Functions both of which supplement the FH Help.
In many cases, some parameters are DATA REFERENCES, which has a help page nearby called Understanding Data References.
Also see the Knowledge Base page Creating a Query that has links to Knowledge Base pages on Understanding Data References and Understanding Functions both of which supplement the FH Help.
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Query for deaths as a result of Childbirth
Thanks Tatewise, I have looked at those pages in the knowledge base but they lso refer to other articles under Family Historian Help > Using Family Historian > Advanced Topics > Expressions and Contexts of Use
Ive looked under Help on the menubar but dont have a Using Family Historian option under it or am i looking in the wrong place
TimTreeby I copied and pasrted that last expression into the expresion box and it tells me that its invalid when i try to add it.
very frustrating to be so near a solution but not finaly there.
I am using version 3 of FH, thats not the problem is it.
Ive looked under Help on the menubar but dont have a Using Family Historian option under it or am i looking in the wrong place
TimTreeby I copied and pasrted that last expression into the expresion box and it tells me that its invalid when i try to add it.
very frustrating to be so near a solution but not finaly there.
I am using version 3 of FH, thats not the problem is it.
- tatewise
- Megastar
- Posts: 28449
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Query for deaths as a result of Childbirth
OH DEAR David!!!!!
We wish you had said that earlier!
Or better still posted your questions under Forums > Version 3 Usage.
Then we could advise you so much better, and you would get to a solution so much quicker.
FH Version 3 does not support the =Timespan() Function.
The help is at Help > Contents and Index > General Topics > Advanced Topics > Understanding Functions.
To pursue the more advanced Queries you are now attempting I would advise upgrading to FH V5.
We wish you had said that earlier!
Or better still posted your questions under Forums > Version 3 Usage.
Then we could advise you so much better, and you would get to a solution so much quicker.
FH Version 3 does not support the =Timespan() Function.
The help is at Help > Contents and Index > General Topics > Advanced Topics > Understanding Functions.
To pursue the more advanced Queries you are now attempting I would advise upgrading to FH V5.
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Query for deaths as a result of Childbirth
I am so sorry for not mentioning that earlier. Yes I think you are right I do need to upgrade to version 5 .
Nevertheless I have learnt a great deal with your help and I'm sure it will stand me in good stead.
Thank you so much for all the support you have given me. It's very much appreciated.
Nevertheless I have learnt a great deal with your help and I'm sure it will stand me in good stead.
Thank you so much for all the support you have given me. It's very much appreciated.