* Struggling with a complex query

[RogerF]

I'm trying to identify Individuals with missing 1841 Census events. The query I think I need is roughly:

(DoB < 1841)
AND
(Exists(1851 Census event) OR Exists(1861 Census event) OR Exists(1871 Census event))
AND NOT
(Exists(1841 Census event))

Needless to say, I've failed to get anywhere close. Guidance would be much appreciated.

[Jane]

Why are you looking for later census facts, someone who died in 1845 would still be missing a census, even though they don't have later census details.

I presume you have already tried
fhugdownloads:contents:1841_census_data_search|> Query:Individual ~ Alive at 1841 Census

[tatewise]

Another approach is to use the Lookup Missing Census Facts Plugin that identifies all missing Census Events.

Also see fhugdownloads:contents:kinsfolk_census_queries|> Query:Individual ~ Kinsfolk Census Queries for Kinsfolk Census To Do Table.

Likewise, what is the relevance of later Census Events?
The basic test is if DoB < 6 June 1841 and DoD > 6 June 1841 and Census[year=1841] does not exist.
i.e.
Exclude unless INDI.BIRT.DATE < 6 June 1841
Exclude unless INDI.DEAT.DATE > 6 June 1841
Exclude if exists INDI.CENS[year=1841]

[RogerF]

The relevance of the future census events is that they show that the individual was alive beyond 1841; that's much better than merely wondering, with no proof either way.

[tatewise]

Yes, but if they died between 1841 and 1851 or simply have no Census records at all, then you would exclude them even if they have no 1841 Census entry.

Even if you have no actual DoD the Estimated Death Date function will give you a workable date.

[Gowermick]

Roger,
After running the query, you'll still need to look at each individual to get the details needed to make the search (which is point of the exercise after all). At that point you can see quickly see if they have a later census and adjust your thinking accordingly. I think adding these tests to the query just complicates it, and adds nothing of value.

Mike
Your suggestion is a bit simplistic, as it misses out those people for whom we have no death date

Include if birth<1841
Exclude if death < 1841 (keeping those we're not sure have died yet)
Exclude if birth < 1741 (doubt they'd still be alive! Adjust accordingly)
Exclude if exist Cens=1841

[RogerF]

Thank you all for pointing me in the right direction. This seems to do pretty much what I want:

Add if
=IsTrue(Exists(%INDI.CENS[year=1851]%) or Exists(%INDI.CENS[year=1861]%) or Exists(%INDI.CENS[year=1871]%))
is true

Exclude if
=Exists(%INDI.CENS[year=1841]%)
is true

Exclude if
%INDI.BIRT.DATE:YEAR%
is greater than 1840

Exclude unless
=IsTrue(ContainsText(%INDI.BIRT.PLAC%,", BRK",STD) or ContainsText(%INDI.BIRT.PLAC%,", OXF",STD))
is true

At any rate, it focuses me on 1047 Individuals who need some TLC.

[tatewise]

Mike Loney, I know my example of testing actual Birth & Death is simplistic but just wanted to establish basic strategy.

That is why I added the later suggestion of using =EstimatedBirthDate() and =EstimatedDeathDate() functions that use actual Birth or Death dates if they exist, but otherwise use Baptism, Christening, Burial, Cremation dates, and if none of those dates exist then uses dates of close relatives plus Tools > Preferences > Estimates > Life Span, and saves a great deal of complex expressions. But there is the caveat that Dates such as Before 1800 or After 1800 are not handled correctly.

That is why I suggested Lookup Missing Census Facts as it takes all the above into account, including workarounds for mishandled Dates, and provides the Search Queries for several online databases, which presumably is the eventual TLC objective that Roger wants. It takes account of married women's surnames needed for Census searches. Plus it has options to focus on particular Individuals or Ancestral branches. Cannot understand why Roger is not using that Plugin.

[RogerF]

Roger isn't using Lookup Missing Census Facts (though I did try it earlier) simply because the query approach provides a much more focused result set. It's given me a long list of individuals, sorted by descending DoB, where one click shows me all of the facts I currently have on each individual -- from which it's immediately clear what's missing -- while the family context suggests optimal search strategies . Horses for courses, Mike; one size doesn't fit all.

[Gowermick]

Exclude if
%INDI.BIRT.DATE:YEAR%
is greater than 1840.

This really neeeds adjusting to 1841, to catch those born betweeen. 1/1/1841 and 6/6/1841