![\"\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2022\/01\/2022-01-17-10_05_30-Window.png\")
<\/a><\/p>\nWhere there is a blank in the Kit# column, that usually means FTDNA. The newest date I have on my list is 11\/21\/2019.<\/p>\n
Sorting My FTDNA Matches<\/h2>\n
I’ll try this option:<\/p>\n
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:<\/p>\n 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:<\/p>\n 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:<\/p>\n That gave me a spreadsheet like this:<\/p>\n 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.<\/p>\n First I save the two tables and open Access. I have a lot of databases, but the one called AutosomalDNA looks good:<\/p>\n 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.<\/p>\n 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:<\/p>\n Next I choose Create>Quesry Design and add the two tables to my new Query:<\/p>\n I would like the ouput to look somewhat like my master spreadsheet. Here is my first shot at a query:<\/p>\n This query was not very good:<\/p>\n 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.<\/p>\n My easy fix is to create a right hand join:<\/p>\n <\/p>\n 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.<\/p>\n 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:<\/p>\n I tried that and got this query:<\/p>\n This gives me 6898 lines, so it will have to do. Some names seem to be repeats of what I have.\u00a0 I copied the results of this query to the bottom of my spreadsheet:<\/p>\n I added today’s date for the match date. Then I sorted by Chromosome and Start Position.<\/p>\n 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.<\/p>\n 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:<\/p>\n 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:<\/p>\n 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.<\/p>\n 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.<\/p>\n 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:<\/p>\n 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.<\/p>\n I tried this to copy large amounts of information from this spreadsheet to mine:<\/p>\n I also added test company and the match date (though this would be the download date). My spreadsheet now has over 58,000 rows.<\/p>\n By looking anew at my 23andMe list, I was able to add a person to my Hartley DNA Tree:<\/p>\n Simone is on the Robert Hartley branch which I didn’t have on my tree before.<\/p>\n 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.<\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nCombining the Two Downloaded Tables with MS Access<\/h2>\n
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nSecond Try<\/h3>\n
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nMaternal or Paternal Matches?<\/h3>\n
23andMe Matches<\/h2>\n
<\/a><\/p>\n<\/a><\/p>\nAdding MyHeritage DNA Matches<\/h2>\n
<\/a><\/p>\n<\/a><\/p>\nSome Applications<\/h2>\n
<\/a><\/p>\n