* amend census custom query

[jsphillips]

Dear all
On the custom query list is a query called :-
Custom_missing-years_1911
This shows all persons with bmd dates and census columns from 1841 to 1911.
For all those persons who died before 1841 or who were born after 1911...can I exclude these from the list to only show 'qualifying persons' for census.
What line/lines do I need to add to query please
Thanks
John phillips

ID:5388

[Jane]

Just add

exclude if

%INDI.DEAT.DATE% before 1841

exclude if
%INDI.BIRT.DATE% after 1911

[jsphillips]

Sorry being a bit stupis
it then reads 'not recognised as a valid tag address..??
under column I entered ..... exclude if

under expression ..I entered .....%INDI>DEAT>DATE% before 1841
Have I miscobstrued the way to enter this ??
Thanks

[jsphillips]

sorry I did enter %INDI.DEAT.DATE% before 1841
Also sorry about my misskeying
John

[PeterR]

The filter needs to be built as follows:

[Jane]

Sorry John, I forgot you don't need to the % % on expressions, the easy way to enter them is to select them from the left hand panel.

[jsphillips]

Sorry I give up
Tried to use row ..In condition used Exclude if
Expression INDI>DEAT(1).DATE
Value 5 JUNE 1841
in operator it will not let me put in 'was earlier than'
Is there some simple instructions in the GUIDE for using this etc
If not I would like someone to treat me as a simpleton with simple instructions please
Sorry
Thanks again
John

[Jane]

You have round brackets and a > in that expression.

Instead of typing it, click the ... button on the end and select

Events then Death then Date and double click it and click Ok.

[jsphillips]

OK that goes in ok now...THanks a lot.
How do I get the next line you suggested :
exclude if
%INDI.BIRT.DATE%
greater than
1911
I cannot find from the button the combination of the DEAT & BIRT, also tried typing and does not work.

The point of the exercise was to produce the Custom_Missing_Years_1911 but to exclude ALL persons from the list who were
Born and died before 1841 and
Born and died after 1911.
Thanks for being so patient
Best regards
John

[Jane]

Just swap DEAT for BIRT

Not sure why you can't use the Data Reference assistant, you just click on the + to drill down to the field you want.

[tatewise]

Since the standard Census_Missing_Years_1911 includes Birth Date and Death Date in the Result Set it is even easier.

On the Rows tab, at the right-hand end of the Expression box, there is a black triangular button.

Click on that triangular button and select %INDI.BIRT[1].DATE% from the list.
Set Condition to Exclude if.
Set Operator to was later than.
Set Value to 1911 or whatever date you prefer.
Click on the Add button bottom right.

Click on that triangular button and select %INDI.DEAT[1].DATE% from the list.
Set Condition to Exclude if.
Set Operator to was earlier than.
Set Value to 1841 or whatever date you prefer.
Click on the Add button bottom right.

Run the Query and the results will only show 'qualifying persons' for census years.

[jsphillips]

Thank you Tatewise just what I needed very concise for a beginner like myself. Also helped for me to understand.
One question though ..
(1) Does this work with whole year dates like 1841 or does it need a precise date eg 31/12/1840 etc ??
(2) Does it work where an 'approx' date or date range has been used??
What I was trying to achieve was a list of 'qualifying' persons who had a possible census date(s) in their range who had not yet been 'census'd'
So the query needed to pick up :-
(a) anyone who was living between 1841 and 1911 but died after 1911
(b) anyone who was living between 1841 and 1911 but died before 1911.
I stiil find the query mentioned does not work in this way
Does it need a 'AND' qualifier ??
Thanks
John

[tatewise]

(1)
You can specify any simple Date in the Query Value box.
Just like everywhere else in FH, click on the ... ellipsis at right-hand end of the Value box and the Date Entry Assistant will help you define your Date.
You can omit or include the Day or Month fields.

(2)
Yes, it works with any Birth/Death Date format except a Date Phrase without an interpreted as option.

(a) & (b)
The Query needs to pick up anyone who was alive between the 1841 and 1911.
It does not matter whether they died before 1911 or after 1911.
The Exclude if %INDI.DEAT[1].DATE% was earlier than 1841 includes everyone who died (i.e. was alive) after 1841.
The Exclude if %INDI.BIRT[1].DATE% was later than 1911 retains everyone who was born (i.e. was alive) before 1911.
The two Row conditions effectively AND together leaving only those alive between 1841 and 1911.
If the Query is not working this way, double check that the DEAT & BIRT, earlier and later, and 1841 and 1911 values are correctly associated.

You may be interested in the related Kinsfolk Census Queries in the Knowledge Base at:
http://www.fhug.org.uk/wiki/doku.php?id ... us_queries
These put 'n/a' in the results where an Individual is 'not alive' at any Census date.
So it shows exactly which Census years are missing where an individual was alive.

[jsphillips]

Tatewise
Thanks again for your help
I realise that where I have no Birth or death date in a record this still shows on the result.
What is the way to exclude all persons who have neither a birth or a death date please
Yes I will look at Kinsfolk census queries but I would prefer not to look at the reults including mthese persons
Best regfards
John

[jsphillips]

sorry much finger keyboard problems.
I would rather exclide than have an n/a result.
Thanks

[tatewise]

Add the following Row to exclude anyone with no birth date and no death date.

Exclude if
=Bool( Not(Exists(%INDI.BIRT.DATE%)) and Not(Exists(%INDI.DEAT.DATE%)) )
is true

Look at the Kinsfolk Census Queries screenshot and you will see that the n/a applies to each and every Census Year.
So someone born in 1855 will have n/a for 1841 Census and 1851 Census but not the later Census years.