* Working With Queries
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Working With Queries
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
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
- LornaCraig
- Megastar
- Posts: 3190
- Joined: 11 Jan 2005 17:36
- Family Historian: V7
- Location: Oxfordshire, UK
Working With Queries
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)
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)
- tatewise
- Megastar
- Posts: 28341
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Working With Queries
I don't think you need both HUSB & WIFE - just use:
Exclude if
INDI.~SPOU[1]>REFN[1]
Is null
Exclude if
INDI.~SPOU[1]>REFN[1]
Is null
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Working With Queries
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
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
- tatewise
- Megastar
- Posts: 28341
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Working With Queries
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.
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.
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Working With Queries
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]
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]
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Working With Queries
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?
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?
- Jane
- Site Admin
- Posts: 8508
- Joined: 01 Nov 2002 15:00
- Family Historian: V7
- Location: Somerset, England
- Contact:
Working With Queries
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
This should give you the basics
https://www.dropbox.com/s/s8xirxa6s2rgy ... cestor.fhq
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Working With Queries
I think thats a plug in for version 5. Unfortunately I'm still running version 3
- tatewise
- Megastar
- Posts: 28341
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Working With Queries
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.
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.
- LornaCraig
- Megastar
- Posts: 3190
- Joined: 11 Jan 2005 17:36
- Family Historian: V7
- Location: Oxfordshire, UK
Working With Queries
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.
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.
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Working With Queries
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.
That's what I love about Family Historian. I've been using it for 5 years and am still learning new things about it.
-
- Famous
- Posts: 116
- Joined: 01 May 2005 18:29
- Family Historian: V5
Working With Queries
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]
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]