I've been searching for threads or How To's for the creation of a spreadsheet from a custom query eg Census Record Checklist-All but haven't found anything to help me get started.I'm sure there has been something on this before but cannot find it.
Can anyone point me in the right direction please?
Dagwood
ID:6029
* Creating a spreadsheet from a custom query
Creating a spreadsheet from a custom query
Run your query.
Highlight all cells you wish to copy.
Click on 'Copy selected cells' on the menu bar (to the right of the 'run query' arrow).
Open your spreadsheet.
Paste data & add headings etc.
Highlight all cells you wish to copy.
Click on 'Copy selected cells' on the menu bar (to the right of the 'run query' arrow).
Open your spreadsheet.
Paste data & add headings etc.
-
Jane
- Site Admin
- Posts: 8442
- Joined: 01 Nov 2002 15:00
- Family Historian: V7
- Location: Somerset, England
- Contact:
Creating a spreadsheet from a custom query
Or from the Query menu button select Save As and select CSV file which can then be opened in Excel.
Creating a spreadsheet from a custom query
Many thanks Jane, method it did the trick but I will have a go at the CSV approach next time.
Dagwood
Dagwood
Creating a spreadsheet from a custom query
I've hit a problem. My intention was to have a spreadsheet with all Census and Certificates shown as being found on Ancestry and/or copies available. This, along with dates for birth and death would show me what gaps I have both in Family Historian and Ancestry. The initial copying of data from Census Record Checklist All worked well but of course left out all people born after 1911 and who died before 1941
Then,of course the minute I added someone new to FH they were not in the spreadsheet.
So, I might start again if only to use the Records Window to get everyone on the spreadsheet and then use other queries to get the info on census and BMD certificates etc, and accept that new people will be added manually as the spreadsheet is not dynamic. Or can it be?
Am I being too ambitious or is there a better way of doing this? I'm sure others have done this already.
As the old joke went is it a case of ' If I were you I wouldn't have started from here...'
Any thoughts or suggestions most welcome as I have not gone far enough to worry about starting again.
Dagwood
Then,of course the minute I added someone new to FH they were not in the spreadsheet.
So, I might start again if only to use the Records Window to get everyone on the spreadsheet and then use other queries to get the info on census and BMD certificates etc, and accept that new people will be added manually as the spreadsheet is not dynamic. Or can it be?
Am I being too ambitious or is there a better way of doing this? I'm sure others have done this already.
As the old joke went is it a case of ' If I were you I wouldn't have started from here...'
Any thoughts or suggestions most welcome as I have not gone far enough to worry about starting again.
Dagwood
-
tatewise
- Megastar
- Posts: 27088
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Creating a spreadsheet from a custom query
Once you export the FH Query results to a spreadsheet the data in the spreadsheet is static.
There is no way to make it dynamic.
You must run the FH Query again, and export the results to update the spreadsheet, for any new data not just new Individuals.
It might be possible to create a V5 Plugin to automate the same job, but it will still have to be run every time the FH data changes.
It is not clear where your Ancestry data is stored.
Is this information you collect external to FH, or is it somehow recorded in FH Records somewhere.
Could you add the Ancestry information to FH as a Work In Progress list, or To Do Attribute of Individuals?
Techniques for this have been discussed in other threads and at WiP & To Do lists.
If all the information is within FH then an external spreadsheet is not needed.
All data can be reported using FH Queries (or V5 Plugins), and is 'dynamic'.
There is no way to make it dynamic.
You must run the FH Query again, and export the results to update the spreadsheet, for any new data not just new Individuals.
It might be possible to create a V5 Plugin to automate the same job, but it will still have to be run every time the FH data changes.
It is not clear where your Ancestry data is stored.
Is this information you collect external to FH, or is it somehow recorded in FH Records somewhere.
Could you add the Ancestry information to FH as a Work In Progress list, or To Do Attribute of Individuals?
Techniques for this have been discussed in other threads and at WiP & To Do lists.
If all the information is within FH then an external spreadsheet is not needed.
All data can be reported using FH Queries (or V5 Plugins), and is 'dynamic'.
Creating a spreadsheet from a custom query
I hope I understood this correctly. You want to look at a spreadsheet of FH data and compare this to a spreadsheet of Ancestry data. You also want to use said spreadsheets individually so you know what data Ancestry is missing and what FH is missing (and possibly see if you have that data in the other set of records)
A spreadsheet by design is static data. It is possibly to link it to outside data, but not to a gedcom. So I'm afraid you're stuck on manual updating it.
As to Ancestry, I've started a project with the sole purpose of downloading Ancestry gedcom and 'merging' them into the same project every time, which will then allow me to do the same to that data as to my main project. Probably not the tidiest way, but it allows me to keep my verified data separate from Ancestry pitfalls. Just a minor note of caution. Merging your FH data into the Ancestry project and then uploading the result into Ancestry may give you duplicate births & deaths. Ancestry can't tell app from est and therefore FH doesn't merge them automatically.
A spreadsheet by design is static data. It is possibly to link it to outside data, but not to a gedcom. So I'm afraid you're stuck on manual updating it.
As to Ancestry, I've started a project with the sole purpose of downloading Ancestry gedcom and 'merging' them into the same project every time, which will then allow me to do the same to that data as to my main project. Probably not the tidiest way, but it allows me to keep my verified data separate from Ancestry pitfalls. Just a minor note of caution. Merging your FH data into the Ancestry project and then uploading the result into Ancestry may give you duplicate births & deaths. Ancestry can't tell app from est and therefore FH doesn't merge them automatically.
Creating a spreadsheet from a custom query
Hi, thankyou both for your answers and your questions.
This has got me asking myself :
i. What is it I'm trying to achieve and how do I go about getting there?
ii. Where is the information coming from and how easy will it be to maintain it?
iii. Is it all going to be worthwhile?
I started by thinking I could download a set of records from FH which would give me a complete set of all the peeps I have on FH. Using that I would check this against those in the 5 trees my daughter and I are researching in Ancestry. There was only ever going to be one spreadsheet and hopefully that would act as a checklist for ensuring there would be no gaps in Census records or BMD certificates found through Ancestry or in our possession.
When we find a record in Ancestry I copy it into the media folder in FH. I had never thought of merging gedcoms from Ancestry to FH.
In summary I think I need to spend some time thinking about which query gives me the most information to start the spreadsheet and how easy it will be to continue its maintenance.
Away to the thinking chair now!
Thanks again
Dagwood
This has got me asking myself :
i. What is it I'm trying to achieve and how do I go about getting there?
ii. Where is the information coming from and how easy will it be to maintain it?
iii. Is it all going to be worthwhile?
I started by thinking I could download a set of records from FH which would give me a complete set of all the peeps I have on FH. Using that I would check this against those in the 5 trees my daughter and I are researching in Ancestry. There was only ever going to be one spreadsheet and hopefully that would act as a checklist for ensuring there would be no gaps in Census records or BMD certificates found through Ancestry or in our possession.
When we find a record in Ancestry I copy it into the media folder in FH. I had never thought of merging gedcoms from Ancestry to FH.
In summary I think I need to spend some time thinking about which query gives me the most information to start the spreadsheet and how easy it will be to continue its maintenance.
Away to the thinking chair now!
Thanks again
Dagwood
-
Jane
- Site Admin
- Posts: 8442
- Joined: 01 Nov 2002 15:00
- Family Historian: V7
- Location: Somerset, England
- Contact:
Creating a spreadsheet from a custom query
1st question, why do you have 5 trees, do any people repeat if so it's probably better to have a single tree.
If your daughter and you are unlikely to actually edit the tree at the same time, you could use a shared dropbox so you could both maintain the same file, but you will need a discipline to lock and unlock your tree when you are editing it.
I have some ideas for this which could be implemented using a plugin, so when you are going to edit rather than look at data you lock the tree and unlock it when you have finished.
If your daughter and you are unlikely to actually edit the tree at the same time, you could use a shared dropbox so you could both maintain the same file, but you will need a discipline to lock and unlock your tree when you are editing it.
I have some ideas for this which could be implemented using a plugin, so when you are going to edit rather than look at data you lock the tree and unlock it when you have finished.
Creating a spreadsheet from a custom query
Feel like I need to clarify something here. Ancestry gedcoms do not import the images, only the website links to images in the source and the info. Hence I have an ancesrty project and a FH clean project. Then I can see what's missing by comparing. But I do that only occasionally. In general, I work like you and copy them. But to compare, I download the gedcom from Ancestry. I NEVER merge the Ancestry tree into my clean FH project. Did that once and I've never been so greatful for back-ups! What a mess!Dagwood said:
When we find a record in Ancestry I copy it into the media folder in FH. I had never thought of merging gedcoms from Ancestry to FH.
Creating a spreadsheet from a custom query
Jane, I think you may have misunderstood where my 5 trees are. They are all in Ancestry, not FH, and are kept seperate purely because they are all public and we don't see any need for interested ( ie invited guests) people to see trees they have no connection with.
Ultimately all the people in all the trees, if connected in any way to the family, are shown in the one FH tree as I agree with you this is the best way to handle them. My daughter does not edit the FH tree just Ancestry so we have no problems with sharing FH.
Merenwen, thanks for the clarification. Can I ask you to elaborate on how you use the Ancestry gedcom to compare what you have in Fh? Do you download to a second tree and then run a merge/compare with your 'clean' tree?
Thanks
Dagwood
Ultimately all the people in all the trees, if connected in any way to the family, are shown in the one FH tree as I agree with you this is the best way to handle them. My daughter does not edit the FH tree just Ancestry so we have no problems with sharing FH.
Merenwen, thanks for the clarification. Can I ask you to elaborate on how you use the Ancestry gedcom to compare what you have in Fh? Do you download to a second tree and then run a merge/compare with your 'clean' tree?
Thanks
Dagwood
Creating a spreadsheet from a custom query
I'll try Dagwood, but words aren't my strong point.
I have 1 project that's my 'clean' file. It's all the information I have found in records, certificates etc.
I have a second project called Ancestry. I started with the gedcom from Ancestry of one of my trees (like you, I have multiple ancestry trees, but for different reasons, won't bore you with them). I then merged the other Ancestry trees into it.
When I find a new record on Ancestry, I download the image from Ancestry, add the data in FH to my clean project as well as the ancestry tree online. Once every while, dependant on how much I've been doing, I'll download the gedcoms again and merge them with the ancestry project.
At this point I have 1 clean project and 1 ancestry project. I'll then run queries on both projects and export them to excel. Queries will include BMD dates & places, census records, christening & burials, etc.
I get clever with excel and copy both files into one. A quick sort and conditional formatting later and it will show me records that don't match each other. I then go back to the place I have the data, look up the source and add it to FH if I trust said source.
It might seem a long way round, but my tree is shared and linked with a lady in Australia. I never used to be this careful about my clean tree and would simply merge Ancestry into the FH project and ended up deeply regretting it! If you're certain of the information that's coming from Ancestry, you can do a merge and run queries on whether or not you have any linked media.
I have 1 project that's my 'clean' file. It's all the information I have found in records, certificates etc.
I have a second project called Ancestry. I started with the gedcom from Ancestry of one of my trees (like you, I have multiple ancestry trees, but for different reasons, won't bore you with them). I then merged the other Ancestry trees into it.
When I find a new record on Ancestry, I download the image from Ancestry, add the data in FH to my clean project as well as the ancestry tree online. Once every while, dependant on how much I've been doing, I'll download the gedcoms again and merge them with the ancestry project.
At this point I have 1 clean project and 1 ancestry project. I'll then run queries on both projects and export them to excel. Queries will include BMD dates & places, census records, christening & burials, etc.
I get clever with excel and copy both files into one. A quick sort and conditional formatting later and it will show me records that don't match each other. I then go back to the place I have the data, look up the source and add it to FH if I trust said source.
It might seem a long way round, but my tree is shared and linked with a lady in Australia. I never used to be this careful about my clean tree and would simply merge Ancestry into the FH project and ended up deeply regretting it! If you're certain of the information that's coming from Ancestry, you can do a merge and run queries on whether or not you have any linked media.
Creating a spreadsheet from a custom query
You could have fooled me!Merenwen said:
I'll try Dagwood, but words aren't my strong point......
That was very clear and I thank you for taking the time to explain it so thoroughly.
Dagwood
Creating a spreadsheet from a custom query
Hi Merenwen,Merenwen said:
................
I have a second project called Ancestry. I started with the gedcom from Ancestry of one of my trees (like you, I have multiple ancestry trees, but for different reasons, won't bore you with them). I then merged the other Ancestry trees into it.
When I find a new record on Ancestry, I download the image from Ancestry, add the data in FH to my clean project as well as the ancestry tree online. Once every while, dependant on how much I've been doing, I'll download the gedcoms again and merge them with the ancestry project.
At this point I have 1 clean project and 1 ancestry project. I'll then run queries on both projects and export them to excel. Queries will include BMD dates & places, census records, christening & burials, etc.
................
Back again! Can I just query with you whether you are able to create a Gedcom from Ancestry.co.uk or whether you meant Ancestry ie your file on FH? I only ask because although I thought I had seen an option to create a gedcom I now see it was for uploading one into Ancestry.co.uk and I can't see any way to create one from it.
Thanks
Dagwood
-
tatewise
- Megastar
- Posts: 27088
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Creating a spreadsheet from a custom query
Open the main Ancestry page with a toolbar that offers Home, Family Trees, Search, DNA, etc.
Click on Family Trees itself - NOT any of its drop-down options that lists your trees.
This will list your trees in a new page with a Manage Tree link against each one.
Click the Manage Tree link for the tree you want to export and on the right is an Export tree button for GEDCOM file.
Click on Family Trees itself - NOT any of its drop-down options that lists your trees.
This will list your trees in a new page with a Manage Tree link against each one.
Click the Manage Tree link for the tree you want to export and on the right is an Export tree button for GEDCOM file.
Creating a spreadsheet from a custom query
Thanks Mike, that worked but having looked at the result in FH I'm not sure now that it is going to help me. I appreciate the help you and Merenwen have given but feel as if I'm no nearer to having a means of producing a spreadsheet which enables me to check my FH data against my Ancestry data without running just one query to start it off and then relying on manual updates and insertions from then on. Question is : Which is the best query to start with? A Kinsfolk and add info for B,M,and D's or a full Record list to ensure everyone in FH is there to start with and then add people as they are discovered and also add all source info ie census and certificates found.
Having looked back at earlier answers from yourself and Merenwen I think it best not to try and run periodic queries as there is going to be so much manually added info I'm bound to get rows out of kilter.
I think I'll start with a Kinsfolk and then add the additional names from an All Records list I'll get the people and the rows will space correctly and then I can add columns for putting a tick in to show which records have any of B,M, or D's
I'm waffling on! I guess it's best to do something and stop talking about it. I'll post a note later to say how I got on.
Thanks for all your help guys
Dagwood
Having looked back at earlier answers from yourself and Merenwen I think it best not to try and run periodic queries as there is going to be so much manually added info I'm bound to get rows out of kilter.
I think I'll start with a Kinsfolk and then add the additional names from an All Records list I'll get the people and the rows will space correctly and then I can add columns for putting a tick in to show which records have any of B,M, or D's
I'm waffling on! I guess it's best to do something and stop talking about it. I'll post a note later to say how I got on.
Thanks for all your help guys
Dagwood
-
tatewise
- Megastar
- Posts: 27088
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Creating a spreadsheet from a custom query
If you use one of the Kinsfolk Queries, the way to get every Individual listed is to delete all the Filters on the Rows tab.
If you include a Column named Id with Expression =RecordId() then this gives a unique spreadsheet column that may useful when comparing and merging.
If you include a Column named Id with Expression =RecordId() then this gives a unique spreadsheet column that may useful when comparing and merging.
Creating a spreadsheet from a custom query
I cleared everything on the Rows filters list , added the Id column as suggested,ran the query and......
Bingo! I now have a spreadsheet with all recorded people plus census data and I now feel I'm ready to start adding B,M and D's to it.
A very big Thankyou Mike. I would never have known what to add and delete to those Query filters without your assistance.
Dagwood [smile]
Bingo! I now have a spreadsheet with all recorded people plus census data and I now feel I'm ready to start adding B,M and D's to it.
A very big Thankyou Mike. I would never have known what to add and delete to those Query filters without your assistance.
Dagwood [smile]