* More query help please

[E Wilcock]

Mike kindly coached me in fact queries. i.e. queries that allow an individual to be listed more than once.
But I seem a bit lost with individual queries.

I would like to find married women with children in the census household but whose husbands are not listed in the same census household. That may be due to death, divorce, army service, occupation, prison.

My assumption is that if I expect each individual to appear only once, it is simpler to use an individual query, as it is easier to set up the columns one wants.

[tatewise]

In this case it might actually be easier to use a Family Query as each Family record has links to the Spouses & Children.
Nevertheless, I have not yet ruled out an Individual Query or a Fact Query as the easiest solution.

However, before going any further, the details of the Query depend on how each household is associated with each Census.
Does each Individual record have a Census Event with the same Date, Place, Address and Source Citation?
OR are the Individual records associated by Fact Witness links to the head of household Census Event?

One thing is certain, the Rows tab will need multiple filters, one for each Census[year], and that may not work satisfactorily for the cases where people are in both the 1939 UK Register and 1939 German Census.
Thus it may need a Plugin rather than a Query to produce perfect results, or if the filters get too complex.

[E Wilcock]

Mike I am looking at only one census.
I need to single out by a particular town. I have already, through an individual query, included only people with a census entry for that date and that place.

I dont have witnesses at all. I have not used ancestral sources in these place studies.

My method in the place studies is (I think I was advised to do this as a newbie)
I enter the census for the head of household. I put the census address in the place field. I add the general source for that census and then paste the place field contents into the text from source field.

I then copy that census event (with its citation and text from source field) and paste it into as many other people as I need, all the occupants of that house.

Any other information from the census about an individual e.g. birth date, birth place, occupation, goes into a specific event or attribute of its own.

A subsequent text search (binocular icon) by the address brings up every person listed at that address, even if they are not related.

This system which I think either you or Jane taught me works extremely well.

I cannot be sure that the source citation will be identical for everyone in this particular census as I started by reading films (page images) so entered film numbers and eventually the census went on line first as a partial transcript and later as a much fuller transcript.

Now as more databases have come on line, I am interested in finding the number of absent fathers and may be tracking them down.

[E Wilcock]

There is no hurry - this can easily wait till next week. I dont want to spoil your weekend.

[tatewise]

Can I suggest a slightly different approach.
Use a Query to create a Named List of all Males with a Spouse (and a Child if that is important).
Then use the Lookup Missing Census Facts Plugin and for Choose the Individuals to investigate choose Anyone via the Select Records dialogue and select the Named List.
You can also review the other options to select certain Census Records and lookup web sites.

In the resulting browser window click on any web site column header to sort into Census date order.
Then scroll down to the desired Census year and the fathers of interest should all be there and the links to search the popular online records ready to be used.

There will also be a FH Result Set of fathers of interest, but covering all Census years.

[E Wilcock]

My aim is somewhat different Mike. To find the number of women listed with their children and no husband.

[tatewise]

Sorry, I assumed your last statement:
Now as more databases have come on line, I am interested in finding the number of absent fathers and may be tracking them down.
meant you wanted to search online to track down their census records.

I'll look at it next week.

[tatewise]

I've found time to work on this and think I have a Family Query for you.
See attached Family in Census without Husband.fhq custom query.

It prompts for the Census Date and Census Place that identifies the Census.
The Census Place only needs to partially match with the Place field contents.

If the Wife and at least one of the first 9 Children have a Census Event for the chosen Date and Place, but the Husband does not, then that Family is listed in the Result Set.
It would be easy to check the Wife's or Husband's Father or Mother in addition to the 9 Children.

The Row filters have to use the quite complex GetField(...) function to incorporate the Year extracted from the Census Date into the CENS[year=yyyy] special index so that the correct event instance is selected.

[E Wilcock]

Mike, that is so kind of you and a brilliant help. I didnt understand that it was so complicated.

I didnt want to trouble you by posting again over the weekend but I believed that I had managed this with an individual query. Your query is better as divorced husbands may be elsewhere in the same town. But I would appreciate your looking through my query rows.

First to add if female.
Then exclude unless the census place contains xxxx.
Then exclude if husband's census place also contains xxxx
Then exclude unless birth date was before 1918 (removes any children in the households)
Then exclude if the woman has no children.

It leaves me with a list of mothers on their own with children whose husbands are elsewhere - in the armed forces, emigrated to USA, in prison etc. or divorced or dead.
For people who might want to look at the 1939 register but whose family projects include censuses for other years, one could narrow it further by Census year.

[tatewise]

It is difficult to review your Query filter Rows without the actual Expressions.
However, there are numerous potential problems.

How are you selecting the appropriate instance of the multiple Census Events that any Individual may have?
e.g. Are you using such as %INDI.CENS[year=1901].PLAC% for census in 1901?

How are you coping with multiple husbands where the woman has remarried, i.e. multiple FAMS[n] or ~SPOU[n]?

How are you coping with children from 2nd or subsequent Family partnerships?

Those complications are why I chose a Family Query as that reviews every Wife, Husband, Children relationship.

[E Wilcock]

Thank you Mike. You have provided for proper family historians.

Since I am looking at only one town in one particular census my needs are much narrower.
I have snipped an image of my rows but clearly am not very expert at editing snips to remove the town name. I dont seem to have my wits about me today and cant now remember why I wrote it like that.
My idea was to find women on their own and then check for divorces which in Germany are entered on the marriage certificates..

querycensus2JPG.JPG (28.09 KiB) Viewed 9440 times

[tatewise]

So are you saying that each person has at most only one Census Event?

Your Query is only ever looking at first CENS[1] instance.

Your Query only looks at the first Family FAMS[1] so if the woman has multiple partnerships/marriages FAMS[2], etc, the later husbands & children are ignored.

[E Wilcock]

They only have one census event in that town. Because this is a place study.
But you are right, there might be women with more than one husband. That will affect both rows and columns.

[tatewise]

Forget about the town.
Is it possible that some people (wives, husbands, children) in the Project may have more than one Census Event?
If so, then your Query filters are not working correctly for all cases.

I would be interested in how your Individual Query Result Set compares with my Family Query Result Set.
Is there an extensive overlap?
Does yours list wives/husbands than mine does not, and vice versa?

[E Wilcock]

Sorry Mike. I will get back to this. But other things intervening with school holidays etc. just now.

[E Wilcock]

Mike I apologise for coming back on this.
You helped me write a query showing women listed on the census film with children but whose husbands were not shown on the census. i.e. the husband had no entry - in the query their census was Null.

That census has now gone on line.
So some of the absent fathers now show up and show up but with a different census address, sometimes in the same town e.g. following a divorce. So the census is not null any longer.

I have failed to re write the line. Please can you show me how to exclude the husbands who are not at the same census address as their wives but do have an address on the census?

[tatewise]

I have merged your latest posting with the original thread last July, so there is continuity.
You left that last thread hanging with: "I will get back to this." but no further feedback.

So it is not clear to me which Query you are talking about.
Is it your your Individual Query "Census Families" or my Family Query "Family in Census without Husband"?

I also need to know where you record place & address details.
Does Place just record the Town and the Address field records the house & street address?
Or is everything in the Place field with the Address field empty?

[E Wilcock]

Mike, All my queries are in a right muddle.
I have 10 queries starting with the word census. Because every time I edit one, I save it as a custom query.
I should have marked the one designed by you.
I now see that I may already have what I need. One that compared the actual address of (assumed) husband and wife.
So please ignore this. I am so sorry.
But you have drawn my attention to a potential problem.
I now keep all address details in the place field. However, the Project I am looking at today was my very first effort in fh four years ago.
And I did notice this morning that in some of my early data entry I had entered the Street address in the address field. And not all addresses are accurate as in those days the census was on film written in old German handwriting and now it is on line with the street names properly identified.
I am engaged in tidying up the database.
I am really sorry to have troubled you and thank you for calling up that thread.

[tatewise]

If it helps, the Family Query designed by me has a very different name of Family in Census without Husband.

If you have the full address & place in the Place field, then any of those earlier Queries that check Place should work.
Just ensure that enough address & place detail is included in the filter to differentiate the address of the Wife & Children from that of the (ex-)Husband.

[E Wilcock]

That does help Mike - as I have found it and run it. Thank you.
Your query gives me 86 unexpected missing husbands copied from film - and now hopefully on databases now on line. So it will be interesting. I am so sorry to have forgotten so much.
I made massive databases and am now hopefully writing up what they show.