Announcement

**kaybee930** · 27 December 2006, 08:29 PM

I sent a PM to you re your process.

**s trangsrud** · 28 December 2006, 09:23 PM

Tell me more...(spreadsheet management)

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.

**Victor** · 30 December 2006, 04:48 PM

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

**rdgriffith** · 1 January 2007, 02:48 PM

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.

**Victor** · 1 January 2007, 04:14 PM

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

Announcement

.asp-driven Results Chart

.asp-driven Results Chart

Comment

Comment

Comment

Comment

Comment