Go Back   Family Tree DNA Forums > Group Administrators Forums > Group Administrators - Advanced Chat

Group Administrators - Advanced Chat Discussion forum for Project Group Administrators. Exchange experiences, ideas and interpretation of results.

Reply
 
Thread Tools Display Modes
  #1  
Old 20th December 2006, 02:45 PM
rdgriffith
Guest
 
Posts: n/a
.asp-driven Results Chart

Our Griffi(..) surname project has grown to sufficient size that maintaining the results chart was becoming overly time-consuming. The large table also made it more difficult for participants to compare results. Consequently, I revamped the project website to build the table dynamically from an Access database each time the table is accessed. Now, I simply download the results chart from FTDNA as an Excel spreadsheet and then import the results directly into Access. Upload the new database and Voila!, the results table is updated with any new results.

I also added new functionality that allows a participant to click on their kit number and a program will calculate the genetic distance of the selected kit against all other records in the table...and show those other participants that are potentially related (11-12/12, 22-25/25, etc.).

If other project administrators are interested in this approach, let me know.

Robert D. Griffith
Project Administrator

Griffi(th)(n)(s) Surname DNA Project
DuBose-DuBois Surname DNA Project
Reply With Quote
  #2  
Old 27th December 2006, 08:29 PM
kaybee930 kaybee930 is offline
Registered User
 
Join Date: Jul 2006
Posts: 23
I sent a PM to you re your process.
Reply With Quote
  #3  
Old 28th December 2006, 09:23 PM
s trangsrud s trangsrud is offline
FTDNA Customer
 
Join Date: Apr 2006
Location: Los Angeles
Posts: 181
Tell me more...(spreadsheet management)

Quote:
Originally Posted by rdgriffith
Our Griffi(..) surname project has grown to sufficient size that maintaining the results chart was becoming overly time-consuming. The large table also made it more difficult for participants to compare results. Consequently, I revamped the project website to build the table dynamically from an Access database each time the table is accessed. Now, I simply download the results chart from FTDNA as an Excel spreadsheet and then import the results directly into Access. Upload the new database and Voila!, the results table is updated with any new results.

I also added new functionality that allows a participant to click on their kit number and a program will calculate the genetic distance of the selected kit against all other records in the table...and show those other participants that are potentially related (11-12/12, 22-25/25, etc.).

If other project administrators are interested in this approach, let me know.

Robert D. Griffith
Project Administrator

Griffi(th)(n)(s) Surname DNA Project
DuBose-DuBois Surname DNA Project
I would like more details. I'm co-managing the I1a Project Spreadsheet.
Reply With Quote
  #4  
Old 30th December 2006, 04:48 PM
Victor Victor is offline
Registered User
 
Join Date: Jan 2005
Posts: 473
Robert,

I'm interested in the SQL expression you're using to calculate GD. Also, I'm curious if your Access database has normalized data or just a flat table.
Thanks.

Victor
Reply With Quote
  #5  
Old 1st January 2007, 02:48 PM
rdgriffith
Guest
 
Posts: n/a
Database-driven results table

First, let me add that I am not a programmer or database expert. I know only enough to be dangerous .

My database structure is simple. I use two Access tables whose only common data is the kit number...which is set as the primary key for both tables. The first table contains an exact duplicate of the information contained in the Excel spreadsheet dump of the Classic Chart. The spreadsheet can be imported into Access, using the first row as field headings. The second table contains participant information on earliest proven ancestor, contact data, and pointers to lineage pages, etc. This table is updated each time new participants join the project or provide additional family data. It also contains logical fields for maintaining privacy data on name, email, and phone numbers. These are used to determine whether or not to include contact information in the "Connections" popup.

Don't know if this answers your question, Victor, about normalized data, but it is set up as a relational database for multiple-table queries.

All program code is written in .asp script. My web hosting service is a Windows NT server that supports a variety of database engines, including ODBC drivers...which are invoked by the .asp script to pull data from the Access tables.

The code for calculating GD is an approximation of that used by FTDNA. I asked Bennett for the algorythms they use for GD, but was only received the following explanation: "For the GD we treat all markers under the step wise modal ( http://nitro.biosci.arizona.edu/ftdna/models.html#Step ) except DYS 464 which we treat under the Infinite allele model (http://nitro.biosci.arizona.edu/ftdn....html#Infinite ). That being for the GAP/Members/Genetic distance report, However when calculating the FTDNATiP calculator since a priori we presume that people are related (same name and close distance) we treat all 2 step movements as if they are a single evolutionary event (aka infinite model)."

After a little more research, it is clear that even the experts disagree on the correct way to calculate GD. Consequently, I tried to define an algorythm that came close to producing the same results as the FTDNA Genetic Distance Report. My code uses a stepwise approach for all markers except the 464 series. For markers 464a-d, any difference in markers is treated as a GD of 1. I've found that this approach does not consistently replicate FTDNA results...so they must add a twist or two to the calculation. However, for purposes of identifying possible connections between participants, I believe my calculations are sufficient. The GD code is also written in .asp script.

Updating the tables is pretty simple. Once a week I download an Excel spreadsheet from FTDNA and import it to the Access file...replacing the existing table. Since I have some records for a few people not included in the FTDNA data, I maintain a separate table which I annotate to the end of the newly-imported data. The new database file is then published to the website and the .asp code takes over from there. I use FrontPage as my primary web authoring tool. Total time to update the results data is about five minutes.

I'm happy to share my approach with other project administrators. I contracted an .asp programmer to develop the code for me and it is specific to my website. If any of you are interested in pursuing this approach, I can put you in contact with the programmer, or if you have programming expertise, we can further discuss sharing the program code.
Reply With Quote
  #6  
Old 1st January 2007, 04:14 PM
Victor Victor is offline
Registered User
 
Join Date: Jan 2005
Posts: 473
Robert, dangerous or not you have produced an excellent web site. Congratulations.

Thanks also for the extensive explanation. Now I have a clearer picture of how you're approaching the GD issue. Your comments and experience is very valuable.

Best Wishes for 2007

Victor
Reply With Quote
Reply

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 06:46 AM.


Family Tree DNA - World Headquarters

1445 North Loop West, Suite 820
Houston, Texas 77008, USA

Phone: (713) 868-1438 | Fax: (832) 201-7147
Copyright 2001-2010 Genealogy by Genetics, Ltd.
Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.