* Working With Queries

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

Working With Queries

Post by David_Lewis »

I have been working on charting all the descendents of the Electress Sophia and now want to Find out all the intermarriages between her descendents.

Now fortunately I have given each of her descendents a custom ID .

Ive then constructed a very simple query based on the descendents query. Ive just added a spouse.Custom Id , Spouse.name, and Spouse.birthdate columns.

Now this is a good start but it shows ALL the mariages of The Electress Sophias's Descendents. I would like the output to dispaly ONLY those spouses which have a custom ID. Is this easy to do?

I would also like to be able to have a column showing the date of the marriage but i cant find this where i would expect it to be, under events. any ideas?

Many thanks in anticipation

ID:6350
User avatar
LornaCraig
Megastar
Posts: 3181
Joined: 11 Jan 2005 17:36
Family Historian: V7
Location: Oxfordshire, UK

Working With Queries

Post by LornaCraig »

To show only those spouses who have a Custom ID, try adding these rows:
Exclude if
INDI.FAMS[1]>HUSB[1]>REFN[1]
Is null
Exclude if
INDI.FAMS[1]>WIFE[1]>REFN[1]
Is null

(Edit: Unfortunately this will exclude anyone whose first spouse has no custom ID, even if a second or third spouse does. I'm not sure how to get round that.)

To set a column showing marriage date, use
%INDI.FAMS>MARR.DATE%
(You'll find the data reference under Family as Spouse > Events > Marriage > Date)
User avatar
tatewise
Megastar
Posts: 28272
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Working With Queries

Post by tatewise »

I don't think you need both HUSB & WIFE - just use:
Exclude if
INDI.~SPOU[1]>REFN[1]
Is null
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Working With Queries

Post by David_Lewis »

Thanks Lorna. you were almost right but I needed to include just those who had a spouse with a custom ID and i did that by using INDI.~SPOU[1]>REFN[1].

Works very well but i think i could improve it more.

As I said earlier each descendent of The Electress has a Custom ID so when I now run the query it shows me all the descendents who have married another descendent .....But each marriage is listed twice. for example I have a line showing Elizabeth II marrying Philip Mountbatten but also a line showing Philip Mountbatten Marrying Elizabeth II. It would be nice if I could just have one line per marriage.

I'm guessing what I need to do is exclude a person if they have already been listed as a spouse in the query but I'm not sure if that is possible . any ideas?

Sorry Tatewise I was typing my reply when you posted yours
User avatar
tatewise
Megastar
Posts: 28272
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Working With Queries

Post by tatewise »

One way is to exclude all Female descendants, then only the Male married Female entries will appear:
Exclude if
INDI.SEX
matches
Female

Another approach would have been to use a Family Query instead of an Individual Query.
You may not have been aware that there are 9 Query Types; one for each Record Type - see the General tab in Query window.

See the Knowledge Base on Creating a Query for more advice.
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Working With Queries

Post by David_Lewis »

Now why couldn't I think of something as easy as that[confused]

Sometimes you can get so involved in these things that you miss the simplest and most obvious solutions.

Thank you both very much indeed[smile]
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Working With Queries

Post by David_Lewis »

Ok so far so good but I have hit a problem

I first ran the query described above i.e excluding the females and found 367 such marriages.

I then decided to check and run the same query this time excluding the males and got 395 marriages.

Now a lot of these descendents married 2 or three times and it seems that its only picking up first marriages.

so any ideas?
User avatar
Jane
Site Admin
Posts: 8503
Joined: 01 Nov 2002 15:00
Family Historian: V7
Location: Somerset, England
Contact:

Working With Queries

Post by Jane »

I suspect you will need to build an expression to check all the Families on an Individual Query, so it might be much easier to do a new Family Query, checking that both the Husband and Wife have a custom Id, or simply that they are both descendants of the Empress.

This should give you the basics
https://www.dropbox.com/s/s8xirxa6s2rgy ... cestor.fhq
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Working With Queries

Post by David_Lewis »

I think thats a plug in for version 5. Unfortunately I'm still running version 3
User avatar
tatewise
Megastar
Posts: 28272
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Working With Queries

Post by tatewise »

David, you don't need a Plugin to run a Family Query.

Jane is reiterating what I said earlier about using a Family Query instead of an Individual Query to make finding Family Couples easier.

See the Knowledge Base on Creating a Query for more advice.
User avatar
LornaCraig
Megastar
Posts: 3181
Joined: 11 Jan 2005 17:36
Family Historian: V7
Location: Oxfordshire, UK

Working With Queries

Post by LornaCraig »

David,
The link Jane gave in her post is a link to a .fhq file.  This is a Family Historian Query file, not a plugin.  
It looks as if she has written the query for you, so all you need to do is download and install it.
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Working With Queries

Post by David_Lewis »

Didn't realize that sort of thing was possible in version 3. Will give it a try later this morning.

That's what I love about Family Historian. I've been using it for 5 years and am still learning new things about it.
avatar
David_Lewis
Famous
Posts: 116
Joined: 01 May 2005 18:29
Family Historian: V5

Working With Queries

Post by David_Lewis »

Thank you so much for pointing me in the right direction
Downloaded the query and saved it to a new directory where I keep my FH files. Clicked on the file and it asked me whether i wanted to sinstall it as a custom query. did that and it installed.

added the columns that I wanted then excluded rows that had nulls and it worked a dream.

I now have a list of all the 413 marriages that have taken place between descendants of the Electress (that I know about)

Thank you so so much.[smile][smile][smile]
Locked