* Tutorial on Queries

[E Wilcock]

I am not accustomed to learning computer skills the proper way.

And thought I would post here my experience of the tutorials.
This is Chapter 17 , Introduction to Queries - page 187 in the Book and I have got into difficulties straight away.
The instructions are to select Janet Record (a person in the sample project) then to open the query by going to the View menu select standard query and then select the query to find relations and nearest relations.
There is then a description of the of the Query Parametres dialogue box which opens up and one is asked to OK it.

Fine, except I usually approach the Query by clicking the Query icon and going from there. And indeed on page 188 this is suggested as an alternative entry to the query.

The problem is that, if one does enter by that route, one gets the general screen, but there isnt a button to OK. Or at least I couldnt see one.
So I was mystified but went back and entered via the View menu - and there it was.

I will pursue this tutorial tomorrow but I plan to skip to Chapter 18 on writing queries.

[tatewise]

Yes, with tutorials, if you go off piste, and don't follow the instructions exactly, then the effects will often be different.

You can get there via the Query Window button, but it takes rather longer.

By default the Query Window button opens the first Query alphabetically from the list of available Queries.
So from the Query drop-list near the top you must select Relations and Nearest Relationships from the list.
Then use the blue + triangle Run Query button to the right and obtain the prompt.
You will probably have to use the Starting Person drop-list and select or Browse to Janet Record.
Finally you can click OK.

Going via the View > Standard Queries is quicker, because the list is shorter without Custom Queries, the Run Query is automatic, and the prompt is automatically filled with the pre-selected record, so you just have to click OK.

[E Wilcock]

Yes Mike -
I diverted this morning to watch Jane's video on creating queries. I have watched it before more than once and it is worth mentioning here.

However my usual reaction to looking down the list of Standard Queries and Downloads for custom queries is that they dont start with what most database users would think of as their most usual query. It is said that people who come to fh from other software find it hardest. But we also need to recognise that one gains knowledge in an incremental fashion, adding to what one already knows. People learn to search and filter Access and other databases. And if they dont know how to do it, there is usually someone else around who does. And the output columns are often set up too.

The bog standard query for me in any genealogy software is to find all individuals with a certain value in an Event (or Attribute).
And then to find people for whom a certain event is present within a specified date range.

For me, any section on Queries in the User Knowledge base ought to start by providing those two queries. They may be there already. But I have turned to the Query downloads countless times while using fh and they are not obvious to me.

And the problem with Jane's excellent video is that I would like to be able to refer back to it (to remind myself) with the sound track as text. I am about to watch it again and write down.
I am also going to do the tutorials in Chapter 18.

[tatewise]

Thank you that feedback Evelyn.
The focus on Facts (Event or Attribute) is what lead to the Fact Query type being introduced in FH V4.
There are Standard Queries such as All Facts and All Events but without any filtering.
There is the Standard Query called Contains Text but its Result Set is not very useful.

As far as I can recall you are the first to suggest those two 'bog standard' queries.

They could be easily created from an All Facts query with one of the following Rows filters, then added to the Downloads, and mentioned in the Knowledge Base on queries.

Add if =ContainsText(%FACT%,["Find Text"],STD) is true

Exclude if %FACT.DATE% is null
Exclude if %FACT.DATE% was earlier than ["Earliest Date"]
Exclude if %FACT.DATE% was later than ["Latest Date"]

Some extra Columns may also be needed for say Address and Note fields.

There is also the Edit > Find command, and the Search and Replace Plugin in Search ONLY mode.

[E Wilcock]

Mike -To my shame, I discover that some time ago, I was provided with answers for both these and carefully pasted them into my Word doc guide to using fh.

However I am now starting Getting the Best Chapter 18 tutorials. The problem with books written by the authors of programs is that readers dont understand the meaning. I often skip the bits I dont understand, both in the book and on the forum but not today. An individual query produces a results set of individual records in a specified order -

"The Fact query produces a result set consisting of fields - specifically Fact fields" (p197)

I dont understand how fh users are meant to apply this knowledge - because for me that rules out using it for my query about relative emigration dates. Yet yesterday you designed a facts query for me, and the resulting list was of Individuals.

[tatewise]

The explanation in the tutorial is perhaps an over simplification to avoid confusing newcomers too much.

Yes, an Individual Query focusses on Individual Records but the Columns and Rows can refer to any items including other records that are linked to an Individual Record. So can include, each in different Column, linked Source or Media records, or child or parent or grandparent or grandchild Individual records.
BUT each Row in the Result Set is keyed to just one unique Individual Record and that record is only represented on one Row.

Similarly, a Fact Query focusses on Facts but the Columns and Rows can refer to any items including records associated with a Fact. So can include, each in different Column, the Individual or Family record that owns the Fact, or any Source records cited by the Fact.
BUT each Row in the Result Set is keyed to just one unique Fact and that Fact is only represented on one Row.

Now let us examine some of the differences.

With an Individual Query each Row refers to one unique Individual Record. So to show Facts in the Result Set will require a Column for every field of each and every type of Fact plus each and every instance of multiple Facts such as Census. That could run to dozens if not hundreds or thousands of Columns.

With a Fact Query each Row refers to one Fact. So its fields such as Date, Place & Address only need a few Columns. Its owner record, which could be an Individual or a Family record, can occupy a Column. BUT the same Individual Record can appear on many Rows, once for each Fact that it owns. Similarly, any Source or Media record linked to a Fact could appear on many Rows.

[E Wilcock]

Ah! Now I understand. You just switched a light on for me. Thank you.

I am also on the way to understanding my previous confusion and doubts - that when one wants info about a persons Father, one has to refer back to the family where that person appears as a child and in the query Expression the Father is labelled Husband. This is so counter-intuitive.

[E Wilcock]

I have not understood columns before and concentrated only on rows, filtering the info I needed.
It took me time to understand that for Columns one must use the left hand column, and understand the successive indents - but I have managed to add the mother's birth date to the Tutorial list of people whose parents are called James and Catherine.

One more tutorial to go.

[tatewise]

Because they are needed so often, FH has shortcuts for Father, Mother, Child & Spouse.
In an Individual Query on the Columns tab checkout the Fields on the left.
There you will see those shortcuts with a black arrow icon on their left for all those relatives.
Not only that, but when you expand Family as Child there are options for Father> & Mother> rather than Husband & Wife.

So the following longhand and shortcut data references are equivalent:
%INDI.FAMC[1]>HUSB[1]>% equals %INDI.~FATH[1]>%
%INDI.FAMC[1]>WIFE[1]>% equals %INDI.~MOTH[1]>%
%INDI.FAMC[1]>CHIL[1]>% equals %INDI.~CHIL[1]>%

See how_to:understanding_data_references#shortcuts|> Shortcuts within how_to:understanding_data_references|> Understanding Data References.

[E Wilcock]

Thank you - I saw the short cut arrows but that wasnt clear.

%INDI.FAMC[1]>HUSB[1]>% equals %INDI.~FATH[1]>%

I have finished the tutorial and wonder whether you might like to test my skills (and those of anyone else who educates themselves via this thread) by setting me/us a query to work out and create all on our own.

I realise one may not need to know this, but I do feel cowed when I read the symbols I dont understand. So I am going to return to the other thread and ask you about something there.

There is no hurry for any replies.

[tatewise]

I have created some 'bog standard' fact queries along the lines mentioned earlier.
They are in Downloads fhugdownloads:contents:query_all_facts_filter_by_date_label_or_text|> Query ~ All Facts Filter by Date, Label, or Text.

BTW: That won't appear in fhugdownloads:queriesfact|> Downloads and Links ~ Query Type: Fact until the KB index gets rebuilt, but it will appear in fhugdownloads:recently_added_downloads|> Recently Added Downloads and Links for a while.

Firstly, please confirm they perform as you would have expected.

Secondly, as a challenge, try combining the Rows expressions together to provide say a Date & Label filter, or a Text & Label filter, or even a Date & Text & Label filter.

Additionally, try adding Columns for such as Sort Date, the place Lat/Longitude, or some Source Citation fields, and change the default sort order.

[E Wilcock]

Thank you Mike. I down loaded them.
I did not expect you to provide them. So I Apologise that I did not phrase it well.
When I wrote "to find all individuals with a certain value in an Event (or Attribute)." I meant in a specific event. So I need to add a line to your query Fact filter by label. May be using what you did for finding the text
exclude unless =ContainsText(%FACT%,["Find Text"],STD).

Please dont reply to this I am off to do it. And have done it now.
Moreover I see that if one opts for parameters in two different lines one gets two blank fields to insert the terms.

I dont think I wished for a text search as I use the Find binoculars but it is useful to have just a list of people - I used to have to weed out the other results.

The date works beautifully too. I put in a census day month and year (twice) and got the list.
I will now look at your home work task (or was that it)

[E Wilcock]

Sorry Mike - I need to come back on this.
You have kindly written me three or four Fact queries.

Must each Fact query deal with only one Fact type?

So if you list for me (as you did in two lines) anyone whose parents Emigrated,
I can include and exclude the Indivs who also Emigrated. But I cant then exclude them if they (the child) experienced some other event? Or indeed if they died before a previous date?

I am failing to write such queries (adapted from your own) and I think this may be why.

[tatewise]

You are referring back to the writing queries (15673) thread.
And ask "Must each Fact query deal with only one Fact type?" NO.

The Query in the other thread actually works the other way round.
It filters by Exclude unless =FactLabel(%FACT%) matches Emigration so only people who emigrated are included.
But is further filtered to only include them if their parents also emigrated to a certain place.
So anything associated with the owner of an Emigration fact can be interrogated.

The expression that references the owner Individual record (the child) and associated sub-fields is:
=Field(GetRecord(%FACT%),'INDI.<etc>') where <etc> identifies the sub-field
or
=FieldText(GetRecord(%FACT%),'INDI.<etc>') if you want the sub-field text value.

So to reference any other type of fact use:
=Field(GetRecord(%FACT%),'INDI.<tag>') where <tag> is the GEDCOM tag for that other fact.
e.g.
=Field(GetRecord(%FACT%),'INDI.BIRT') for Birth Event.
or
=Field(GetRecord(%FACT%),'INDI.DEAT.DATE') for Death Event Date.

[E Wilcock]

Thank you Mike. I need to look at this carefully. And due to half term there may be a delay. Just want you to know I appreciate your reply and the tuition.

[E Wilcock]

Have not yet studied this - but still at a basic level, further questions.

Is it possible to open two queries at once so one can copy and paste lines from one to another?

And a question about instances of e.g. census. Does a fact query cover all instances of the fact one is looking for?

[davidm_uk]

I don't think that you can open two queries at once within one instance of FH, but you can open a second instance of FH (even on the same project), and open a different query in each. Then you can copy and paste expressions from one to the other.

BUT - BE CAREFUL if you open the same project twice, as they will both be opening the same GED file, and confusion (maybe even corruption) could arise if both try to update the GED file. May be better to open the second instance of FH on the Sample project, or another "test" project (which could be a temporary copy of your main project - just make sure to give it a name that reminds you which is which).

[tatewise]

"Is it possible to open two queries at once so one can copy and paste lines from one to another?"
YES
But first set Tools > Preferences > Workspaces > Query Window for Re-use of Open Windows Permitted to No.
Then you can open multiple copies of the Query Window. (The same applies to most Workspace Windows.)
Use Window > Cascade so both are visible at the same time.

"Does a fact query cover all instances of the fact one is looking for?"
YES

[E Wilcock]

This is not another problem Mike.
But a heartfelt thank you.
Your "bog standard" queries (plus one I adapted to recover any event by the year it happened) enabled me to fish out some distant people from my family tree whom I want to write about - I last looked at them in 2007, had forgotten almost everything about them and feared they were lost for ever.
You have really saved me today.

[E Wilcock]

Mike - I hope it is OK to put this further Query question in the same thread.

Censuses often show elderly parents living with one of their children.
I have been trying to work out a query that will show where a census for a particular date shows an elderly person living in the same household as their child and possibly grandchildren.

Is this possible?

You showed me how to use a Fact query (starting with the child) to find people whose parents emigrated (or did anything else) and to narrow it down to a particular destination, i.e. to supply the word (field value) on which to filter.

When searching, the census entries on my Project, the address fields would match. But there is no text value for the filter.

I realise this query for a parent living with a child in old age would not be completely comprehensive. But none the less, it would be good to have it.
It could get more complicated if a mother in law or father in law was listed, without their own child being present. For example a widowed man might have his wife's mother in his household helping with the grand children

This is not an urgent question if you are busy over the next few days.

[tatewise]

Before we start solving this Query, the Census recording scheme must be understood.
There are various popular schemes.
1) A separate Census event for every member of the household, with all sharing the same Source Citation.
2) Just one Census event for the head of household, with Fact Witness links to other members of the household.
3) A variant of that 2nd scheme is one Census (family) event for the head & spouse Family of the household.

Until we know which scheme you need to Query then we cannot start.

[E Wilcock]

For the purposes of this research - the first of the above. Using fh (not AS)
I copy and paste the same census entry (and its source) round the family and anyone else in the household.

However not all people, even in the same town, have exactly the same source citation. Where I have read a census transcription, I differentiate between that and one where I have looked at a page image. In the days of using films, I set up a different source for each film of a census. And I still do that. Because I can then label the films with a place name and remember which film is which.

[tatewise]

So, just to confirm, are members of one household linked to one unique Source record?
OR is that Source record sometimes cited by members of other households (perhaps on the same street)?

I am trying to determine if a child and parent living in the same household can be asserted if they cite the same Source record, or might that only assert that they live near each other in different households?

My thinking is that a Method 1 Source Citation should assert that all linked Individuals are in one household.

If that is NOT the case then we can use your originally proposed strategy of comparing Address fields.

[E Wilcock]

Sorry - Mike, I was writing up too hard all week.

I cannot guarantee the approach via citations. I sometimes forget to turn on the grey button for automatic source citation and also I forget sometimes that if I close fh, I turn off the automatic citation too.

[tatewise]

No problem.

This Query is getting a bit more complex, mainly because of multiple Census Events.

The Columns tab needs Expressions to list both parents, probably as you had before.
Father is =Field(GetRecord(%FACT%),'INDI.~FATH>')
Mother is =Field(GetRecord(%FACT%),'INDI.~MOTH>')

The Rows tab filters get quite complex.
First they must add the parents if they are recorded in the same Census as the child.
Now previously you used an expression to obtain the father's EMIGration event Note:
=FieldText( GetRecord(%FACT%), 'INDI.FAMC>HUSB>EMIG.NOTE2' )
and in this thread we learnt that INDI.FAMC>HUSB> equals INDI.~FATH>.

So to obtain a CENSus event we could use:
=Field( GetRecord(%FACT%), 'INDI.~FATH>CENS' )
but that would get the 1st CENSus from the father's list of facts, and might not be the one that matches with the child.

So we need to use the special [year=yyyy] index to select the correct CENSus instance.
That needs the =GetField(...) function that allows its Data Reference to be composed from various parts.
The year index part involves literal text [year= and the year itself Year(%FACT.DATE%) and the end of index ]
=GetField( GetRecord(%FACT%), "INDI.~FATH>CENS[year=" . Year(%FACT.DATE%) . "]" )
Notice how the Year from the %FACT.DATE% is used to select the Father's CENSus.

Thus the Father's CENSus ADDRess is extracted by using:
=GetField( GetRecord(%FACT%), "INDI.~FATH>CENS[year=" . Year(%FACT.DATE%) . "].ADDR" )

So the Rows filter to add the Father's matching Census is:
Add if
=IsTrue( %FACT.ADDR% = GetFieldText( GetRecord(%FACT%), "INDI.~FATH>CENS[year=" . Year(%FACT.DATE) . "].ADDR" ) )
is true
and similarly for the Mother:
Add if
=IsTrue( %FACT.ADDR% = GetFieldText( GetRecord(%FACT%), "INDI.~MOTH>CENS[year=" . Year(%FACT.DATE%) . "].ADDR" ) )
is true

Then exclude all but Census facts:
Exclude unless
=FactLabel(%FACT%)
matches
Census

Finally, exclude young children living with parents:
Exclude if
=AgeAt( GetRecord(%FACT%), %FACT.DATE% )
is less than
15