It has been a while since I have updated my match spreadsheet with FTDNA matches. To see what I have on my exising match spreadsheet in Excel, I sorted by match date:
Where there is a blank in the Kit# column, that usually means FTDNA. The newest date I have on my list is 11/21/2019.
Sorting My FTDNA Matches
I’ll try this option:
Under the Export CSV, there is a filtered option, but that is grayed out. That means I must have to filter my matches first. I’ll choose filter by match date:
I’ll choose 11/22/2019. It turns out that these dates don’t work as all the match dates at FTDNA have been updated. For example, FTDNA shows that I match my mother as of April 18, 2021. I know that I have matched her for much longer than that. If I download my FTDNA match list, I will have many duplicates. In addition, I will be missing email contacts. Also I will be missing detailed Chromosome information:
As I recall, I need to go to the Chromosome browser to get the detailed information. On the Chromosome Browser screen, I didn’t choose a particular person, but chose Download All Segments:
That gave me a spreadsheet like this:
I guess that FTDNA decided to make it not so easy to get people’s emails at some point. Next I need to brush up on my MS Access skills and combine these two tables.
Combining the Two Downloaded Tables with MS Access
First I save the two tables and open Access. I have a lot of databases, but the one called AutosomalDNA looks good:
I need to get my new tables into the Tables section above. I can’t remember if Access is OK with csv files. When I looked for Excel tables, I couldn’t find the csv files, so I’ll convert the files. That involved opening the csv files and saving them as Excel files. Before I could import these files into Access, I had to close them in Excel.
It appears that I am having trouble telling if I imported correctly due to the truncated Table names. I deleted my old files and used the help feature to import the new files. I let Access choose a unique number for each row:
Next I choose Create>Quesry Design and add the two tables to my new Query:
I would like the ouput to look somewhat like my master spreadsheet. Here is my first shot at a query:
This query was not very good:
It looks like there could be more than one person with the initials ‘AB’. Also the query gave me 8168 rows. The Chromosome Browser results gave me 9126 rows. Also I need a total cM column. It appears that FTDNA has taken too much information away, making it difficult to reconstruct a spreadsheet.
My easy fix is to create a right hand join:
When I hit the view button, I get 9,528 rows which is about right. Actually there are 9,326 rows in the Chromosome Browser spreadsheet, so I have some extra rows. This is likely due to ambiguous names or initials in the database.
Second Try
I will try to use just the new Chromosome file and match it with my existing master spreadsheet. Actually, I want the names that are in Chromosome Browser file that are not in my master file. As I recall, there is not a simple way to do that in Access. I think that it takes two queries. Actually, according to Google, there is an easier way:
I tried that and got this query:
This gives me 6898 lines, so it will have to do. Some names seem to be repeats of what I have. I copied the results of this query to the bottom of my spreadsheet:
I added today’s date for the match date. Then I sorted by Chromosome and Start Position.
Maternal or Paternal Matches?
It would have been nice if I could have combined the two tables from FTDNA because there is information on some of the matches as to whether they are maternal or paternal matches. I can go through the new matches one by one and add them to my master match list, but that will take a while.
23andMe Matches
At the bottom of the DNA Relatives list, there is a blue button that says “Download DNA Relatives Data”. After pushing that button I get an email with options:
There are many more options, but I chose “Download DNA Relatives Data”. That gave me a large spreadsheet with a lot of good information. Here are some of the fields I added to my spreadsheet:
Then I re-sorted to integrate the data. I needed to add two new headers at the right of the spreadsheet. These were for Sharing Status and Family Tree. These two items seemed important.
I had been adding a few matches at a time from 23andMe. Now I have the whole list. My spreadsheet now has over 19,000 lines. Some of this information is not important or duplicative, but still it is an improvement.
Adding MyHeritage DNA Matches
While I am at it, I might as well try to add MyHeritage Matches. I made a request to MyHeritages and got my matches. The information from MyHeritage is pretty basic:
The above is for my mother. It gives her ID#, my name, her name, and Chromosome information. I don’t think that I need the RSID numbers. Also, I don’t need the information for my close relatives as they are already on my spreadsheet.
I tried this to copy large amounts of information from this spreadsheet to mine:
I also added test company and the match date (though this would be the download date). My spreadsheet now has over 58,000 rows.
Some Applications
By looking anew at my 23andMe list, I was able to add a person to my Hartley DNA Tree:
Simone is on the Robert Hartley branch which I didn’t have on my tree before.
Lee is a person that I am interested in. He has at least one Hartley in his ancestry and some of his ancestors are from the area that my Hartleys were from.
If I can triangulate his genealogy with other matches on my paternal side near where I match Lee, I may figure out where our common ancestry is.
Summary and Conclusions
- I downloaded detailed Chromosome match information for my DNA matches from FTDNA, 23andMe and MyHeritage
- I had not downloaded 23andMe and MyHeritage matches in the past
- I wish that I had downloaded information on maternal and paternal matches from FTDNA
- I found that FTDNA matches had less information than previously
- I need a way of mining the information that I have.
- The MyHeritage downloads were interesting as they often will have trees attached to the matches.
- I now have over 58,000 rows of DNA matches – though many are duplicates or small matches.