* Query for deaths as a result of Childbirth

Older V3 Queries please post for any version in the General Forum
Locked
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Query for deaths as a result of Childbirth

Post by David_Lewis »

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
User avatar
Jane
Site Admin
Posts: 8507
Joined: 01 Nov 2002 15:00
Family Historian: V7
Location: Somerset, England
Contact:

Query for deaths as a result of Childbirth

Post by Jane »

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.
avatar
TimTreeby
Famous
Posts: 169
Joined: 12 Sep 2003 14:56
Family Historian: V6.2
Location: Ogwell, Devon
Contact:

Query for deaths as a result of Childbirth

Post by TimTreeby »

Had a quick play and i think this is what you want

Image

This gives

Image

With these Columns defined

Image

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.
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Query for deaths as a result of Childbirth

Post by David_Lewis »

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.
User avatar
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

Post by RogerF »

Tim's expression starts =Timespan and ends -3. That may perhaps help.
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Query for deaths as a result of Childbirth

Post by David_Lewis »

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)
User avatar
tatewise
Megastar
Posts: 28335
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Query for deaths as a result of Childbirth

Post by tatewise »

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.
avatar
TimTreeby
Famous
Posts: 169
Joined: 12 Sep 2003 14:56
Family Historian: V6.2
Location: Ogwell, Devon
Contact:

Query for deaths as a result of Childbirth

Post by TimTreeby »

TateWise said
Expression: =Timespan(%INDI.FAMC>WIFE>DEAT.DATE%%INDI.BIRT.DATE%MONTHS)
Correct except he forgot the commas should actually be

Expression: =Timespan(%INDI.FAMC>WIFE>DEAT.DATE%,%INDI.BIRT.DATE%,MONTHS)

Image

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)
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Query for deaths as a result of Childbirth

Post by David_Lewis »

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
avatar
TimTreeby
Famous
Posts: 169
Joined: 12 Sep 2003 14:56
Family Historian: V6.2
Location: Ogwell, Devon
Contact:

Query for deaths as a result of Childbirth

Post by TimTreeby »

Try

=Timespan(%INDI.~MOTH>DEAT.DATE%,%INDI.BIRT.DATE%,MONTHS) as the expression.

As the expression you are attempting to use is incorrect.
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Query for deaths as a result of Childbirth

Post by David_Lewis »

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.
avatar
TimTreeby
Famous
Posts: 169
Joined: 12 Sep 2003 14:56
Family Historian: V6.2
Location: Ogwell, Devon
Contact:

Query for deaths as a result of Childbirth

Post by TimTreeby »

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)
User avatar
tatewise
Megastar
Posts: 28335
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Query for deaths as a result of Childbirth

Post by tatewise »

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.
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Query for deaths as a result of Childbirth

Post by David_Lewis »

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.
User avatar
tatewise
Megastar
Posts: 28335
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Query for deaths as a result of Childbirth

Post by tatewise »

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.
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Query for deaths as a result of Childbirth

Post by David_Lewis »

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.
Locked