RootsChat.Com
General => Technical Help => Family History Programs, Tree Organisation, Presentation => Topic started by: Berlin-Bob on Tuesday 25 January 05 08:35 GMT (UK)
-
I found this tip in the Ancestry Weekly Digest, 22 January 2005
SPREADSHEETS HELP TRACK SURNAMES
When I'm researching a surname that has probably been in one locality for more than one generation, I often make a spreadsheet. The spreadsheet has headings for first and last name, maiden name, birth, marriage, and death dates and places, father's and mother's names, census addresses, and a column for sources. I then plug in every individual with my surname or a variant that I find for that town into the spreadsheet, regardless of whether I think they are directly linked to my current research family or not.
By doing this, I can do such useful things as sorting by parents names, sorting by birthplaces, etc. It's amazing the details you notice when all the facts are in a table, side by side. This is especially true when an individual's name has been altered, mistranscribed, or misspelled on various documents. In addition, while you may not believe that "John Smith" was related to your "Smith" ancestors, there is always the possibility that a new clue will emerge five years down the road. The spreadsheet gives you a quick reference to go back to when assessing new information.
Finally, I am a firm believer in helping other genealogists on their journeys of discovery. If someone searching a similar name in the same location contacts you, you may be able to save them some work or offer a breakthrough on an elusive ancestor.
Carli Francies
Southfield, Michigan
Simple, but effective !
Also from the Ancestry News Digest, for the Copyright-Editor:
REPRINT POLICY:
We encourage the circulation of the "Ancestry Daily News" via non-profit newsletters and lists providing that you credit the author, include any copyright information (Copyright 1998-2005, MyFamily.com, Inc. and its subsidiaries.), and cite the "Ancestry Daily News" (http://www.ancestry.com/dailynews ) as the source, so that others can learn about our free newsletter as well.
-
Here's an idea to supplement this.
If you have all your paper work in ring binders, files, boxes, or whatever (floor ?) then label them clearly.
Add a column to your spreadsheet ('Archive' or similar) with the name of the ring binder, file, box, or wherever you store it and then you can find the original documents again quickly.
Quickly ? well, hopefully quicker than saying "Now where did I put .... ??" ;D
Bob
ps.
Add another column 'Confirmed'. Enter yes, no, or if you wish, a 'degree of certainty'
Using the filter function, you can then very quickly sort out who is a 'definite', and who is just a 'maybe'
pps.
Add more columns to suit: "certificate/documentary proof" (J/N), "to do", etc
-
This is a great idea, Bob!!
I must put it on my to-do list, after I finish inputting stuff into my genealogy software program that I recently bought!
Thank you for sharing this with us!
MarieC
-
Most genealogy programmes give you the facility of calling up a list of names with dates attached. Since I work on computers all day, I would always set up a database rather than a spreadsheet - you can always export a db table or query to a spreadsheet when you need to. So if your genealogy software doesn't give you the flexibility you want, and you have MS Access or something similar, I would recommend using that rather than a spreadsheet.
-
That's a point actually, I prefer databases to spreadsheets because I think they are more flexible and easy to use. I use Open Office and I think there is a database facility there.
will have to go and investigate.
Kerry
-
Bob
One of the problems with spreadsheets comes when you try to sort by date order. Microsoft doesn't like anything earlier than the 20th century.
I have overcome this on my spreadsheets by entering dates as yyyymmdd which solves the problem for me. If anybody knows a better way I'd like to know it.
Peter
-
Peter,
Yes, that is a problem.
I usually solve that by defining two date columns, one as 'text' with a standard format dd.mm.year. Then I can also add dates like "abt. 1900" or '"early 1870's" etc.
For sorting, I use a second column, with your format.
And yes, if anybody knows a better method I would like to know it too.
Suttontrust,
I agree with you that a database solution is even better.
However, I am guessing that many RootsChatter have, and can use, a text processing program, somewhat fewer have, and can use, spreadsheets, and even less have a database program, such as Access.
As Kerry says, there is a database in the newest OpenOffice (version 2) so cost is no problem (it's free !) but the learning curve for many will be steep.
Bob
-
I agree that using databases is a bit specialised, but well worth getting to grips with. I too use OpenOffice 2, and its database facility isn't very sophisticated. I'm lucky enough to have Lotus Approach as well (and MS Access at work). The problem with spreadsheets was highlighted for me when someone sent me all the stuff he'd collected on a particular surname. He hadn't entered info in the columns in a consistent way so it wasn't sortable.
-
I usually solve that by defining two date columns, one as 'text' with a standard format dd.mm.year. Then I can also add dates like "abt. 1900" or '"early 1870's" etc.
I thought that this would be an issue when I began - so I have THREE columns in which to store the date!
Year, Month, Day - then I put the year as a 4 digit number (so far...) like 1754 and then Month as a number too (10 for October) and then the day - if I know all these. Anything without a Month/Day is approximate. Can't do ranges for b/m/d in quarters though...
-
Not really relevant but I thought it might interest some ...
The contributions for the 1901 Leftovers are in excel spreadsheets.
I load them into excel (or OpenOffice) and save them as CSV-files (texts, seperated by commas) which I then upload to the 1901 site
I then use a PHP program I've written to save the data in a database
Anybody accessing the data on the 1901 leftovers site is then accessing a database.
Bob
-
I have Access but dont know how to use it :'( so I use spread sheets
Anna x
-
Microsoft have created a not very sophisticated genealogy program for use with Access but at least it allows the import of GED files, which is quite useful. It comes as a cab file (01076524.cab). Now if somebody could modify it so that was really useful........ :P
Peter
-
Gosh is that how you are supposed to do a family tree :o
Only kidding ;D I never ever got my head round these family tree programs that you can buy i found them complicated to use,i like easy .I had three all bought by well meaning relatives tried all three over a time.I always ended up with an auntie or other relative being my grandmother ;D as for GED coms ::)they are great for quick reference as long as someone does it for me :) My Folders (springbacks) read like a book from when i started in 2000.
Mum Married dad sort of thing,with a marriage certificate. Next the 1881 census with her dad and family on it, as a child, and naval records in between.Plus B.M.D records of both Grandparents.then filled up with printouts from the I.G.I and more census records.For my G.Grandparents,and so it goes on up the tree back to the late 1700.I also have all the letters i have been sent containing records and information.The tree is all in order by person and generation.This year i have been lucky enough to add even more photo of ancestors that were sent to me.These included their burial place's and the headstone. I cannot begin to imagine how i would get that lot a family tree program ??? ;D am i dense,no i like simple ;D
Celia
-
I have certainly found Reunion 8 for Macs to be a good program and easy to use. I haven't got too far with entering information yet, but it is working well and I haven't had to puzzle over much at all.
MarieC
-
That's my problem, I can set up wonderful databases and spreadsheets but the thought of entering in all the information I have defeats me!!!!!
Kerry
-
Oh, I'm glad there is someone else with this problem!! I was really keen to get this software, loaded it in with alacrity, made a start on entering data - and haven't done any more since! It is such a HUGE task!!!
MarieC
-
That sounds a bit like my website, I have several blank pages that I am intending to put stuff on about the places where my family lived plus photos. Have I done anything yet - no!!!!!!
Kerry
-
I started entering data in Excel about 5 years ago. It has its limits, 65,000+ rows per worksheet, so I'd need about 150,000 rows for a Master database. I don't have MS Access so I'd love to hear of any alternatives.
If you're just starting, keep it simple (not what I did). One worksheet each for Births, Marriages, Deaths, Baptisms, Burials, Census etc.
I've just finished the GRO births & deaths for Sampson/Samson - a mammoth task during which time I found data missing due to trying to be too clever so I have to double check the whole lot to be satisfied of getting 100% completion. Theres still 45,000 rows of names though. Not bad going.
What I'm having trouble with is getting all that into a Gedcom. Nothing I've tried, works. I have to manipulate the columns and data so much I risk losing data. I have backups made.
Pete
-
:D All this wonderful, technical info and I still haven't managed my web page.... :-[
-
What I'm having trouble with is getting all that into a Gedcom. Nothing I've tried, works. I have to manipulate the columns and data so much I risk losing data.
Let me try and understand you. You are trying to convert an Excel spreadsheet into a gedcom file? I would have thought that was impossible.
Basically, a gedcom file uses a database consisting of 3 major tables:
Individuals (I numbers)
Groups (F numbers)
Notes (N numbers)
Individuals contains:
Individual ref number (e.g. I0100)
First, middle and surname
where and when born
gender
where and when died details
ancestry ref number (e.g. F020)
descendancy ref number (e.g. F001)
Groups contains:
Husband ref number (e.g. I0100)
Wife ref number (e.g. I0101)
Where and when married
Children ref numbers (e.g. I0300, I0301, I0302, I0303)
Notes contains:
Notes based on the Individual ref number e.g. I0100 is N0100 in the notes table.
All the tables are contained in the one file but they are laid out in a specific area of the file i.e. Individuals, followed by Groups, followed by Notes. There is also a 4th table called Sources (S numbers), which is relatively small.
Organising your data by having separate workbooks for births, marriages and deaths is also fraught with difficulty.
-
There is a utility (ExcelToGedcom) that gives you a very basic layout in Excel, working with Individuals only with Birth & Death data. It doesn't link families. After much tweaking, your spreadsheet data can be saved as a text file that presents the data in a Gedcom format after a bit more tweaking in a word processor. Then paste the whole lot into a 'dummy' gedcom you create that has the vital headers and footers that make a gedcom work. 1,000's of rows of data means months of work can be done in a couple of hours. I tried it once, then twice & worked perfectly at the third attempt with a smaller batch of data. But the instructions you get aren't perfect but it's definitely possible, just with a lot of work if you are comfortable with excel.
What I'm going to attempt next is to add Family groups with Maiden name data, which will be interesting.
Organising your data by having separate workbooks for births, marriages and deaths is also fraught with difficulty
I have one workbook with a few worksheets for each type of data. Excels row limits don't give me much option to keep everything in one workbook or worksheet. So a workbook for births include worksheets for Freebmd births, UKbmd births, IGI etc. Then a workbook for marriages with similar worksheets and so on. I found using one workbook with 20+ sheets was a very large file (75MB) that was liable to crash. It was safer to split the load. I have backups on DVD with everything on it. If I do want to manipulate sheets I simply copy the sheet into a new book, so if it goes wrong, I've not lost anything.
Pete
-
Just want to report a successful conversion of birth data with gender & linked mothers maiden names from excel into Gedcom. ;D 8)
-
I agree that a database is the way to go for large amounts of data, but for most average users it is too complicated.
I am a retired software developer. Over the years I found that my clients preferred simple applications. The only way a database was acceptable was if I created a bespoke application that required nothing more than entering data into a form. For most people, using 'Access' is over complicated and overkill.
Even though I have developed many different database applications in the past, I don't bother to use one for storing this kind of data. I store everything in csv files. I can easily import that into a database if I every need to.
All my data is entered through 'GenScriber' (I fell out with spreadsheets a long time ago).
The csv files that GenScriber creates are organised in folders. Each file is of a reasonable size. They is no need to try and cram everything into a single file.
To retrieve records I use a simple application called 'CsvFileSearch'. It searches through multiple csv files and retrieves individual records.
L
-
Organising your data by having separate workbooks for births, marriages and deaths is also fraught with difficulty.
What are these difficulties? I can't think of any. Could you please amplify.
I have multiple files (hundreds) and see no problems.