Question calculate inbreeding (1 Viewer)

[InFlight]

Hi. I am building a database for bird breeding. Does anyone know of any code / Function to calculate inbreeding and AVK.
Cheers

 

[June7]

And how do you calculate inbreeding? If you can define the algorithm, it can be programmed.

What is AVK?

 

[InFlight]

AVK: This value is 100% when all ancestors are different. The more similar ancestors are, the lower this value will be.

 

[InFlight]

It is the algorithm that i am not sure about.

 

[June7]

I found a website that tries to describe the algorithm but it's higher math over my head. http://www.sobczyk.eu/inb/?lng=en

Programming this to evaluate database records would be way too complicated for me.

 

[InFlight]

Thanks. I found that one to. Hard to get the head around it all. There is also a path system as well but don't know how to implement it.

 

[Minty]

This will require recursion and the correct data stored in your records to be able to identify previous generations. Each potential breeding partner needs to have it's parents ID's recorded, otherwise this can't be calculated.

The recursion can be somewhat troublesome in Access, but if you have access to SQL Server it would be significantly easier.

There are some interesting (NON Access) software links here http://cristal.inria.fr/~ddr/GeneWeb/en/index.html#Par and here http://pypedal.sourceforge.net/

 

[InFlight]

The birds all have ring numbers, none the same. Each pairing has a unique ID with a separate record for each chick.

 

[InFlight]

Hope this helps

 

[InFlight]

Thanks very much. Have a look at the query qry_Pedigee
I am only a beginner at coding so any comments would be great.

 

[InFlight]

No i'm not doing any code on AVK, Relationship or inbreeding yet. still working on it. A lot to do.

 

[InFlight]

Thank you very much. I do have some doubling up which i can fix. The season is the breeding season which here in NZ goes from October to February. Birds breed in February 2019 is still Season 2018 which is the same year on the rings. All birds after fledging go in aviary's so cage number only needs to be once. The maximum number of generations will be 5. This is because of the bloodline in the Gloster canary's which i mainly breed. It starts with a yellow, then Buff 1 to 4, then must be breed back to a yellow to keep good feather type which only comes from a yellow. Should i be using a self referencing table, Which i haven't used before, or the query which i have started to build. Again Thank you.

 

[InFlight]

Hi

Make all the changes you like as i have stopped work on it while you are working on it.
I will spend time on it to understand what you are doing for me. If you remove or change field names i will sort it out later on the forms etc
I finished that qry_Pedgree query and it now takes 1 minute 30 to load on my very quick PC. So may have to look at a different way to do it.
The relationship in the relationship window is that tbl_Bird_Partnerships and tbl_Birds.
When i first started to build the database i had the primary key as ID in tb_lBirds but have since moved on and changed the way i did things so it should be changed as you say.
Cheers

 

[InFlight]

Hi
I wrote this. Is it correct

I split the qry_Pedigree into 2 query's Mothers Side and Fathers Side
( qry_Pedigree_Father & qry_Pedigree_Mother ) and then made another (qry_Pedgree) query joining the 2.
Created a table with 1 field (RingNumber)
Could i have done it with an array

--------------------------------------------------------
Public Function GetAVK(RingNumber As String)
On Error GoTo TestButton_Click_Error

Dim db As DAO.Database
Dim rs, rstPedigree As DAO.Recordset
Dim ii As Integer
Dim ss As String
Dim BirdPair, i, x, varCount As Byte
Dim varAVK As Double

DoCmd.OpenQuery ("qry_DelPedigree")

BirdPair = GetPairID(GetID(RingNumber))

Set db = CurrentDb
Set rs = db.OpenRecordset("qry_Pedgree")
rs.MoveLast
rs.MoveFirst
rs.FindFirst "[PairID] = " & BirdPair
i = 0: x = 0

For ii = 2 To rs.Fields.Count - 1
ss = Nz(rs.Fields(ii).Value, "Nill")
varCount = DCount("[RingNumber]", "tbl_Pedigree", "RingNumber = " & q & ss & q)
If varCount > 0 Then i = i + 1
If ss <> "Nill" Then
x = x + 1
Set rstPedigree = CurrentDb.OpenRecordset("tbl_Pedigree")
rstPedigree.AddNew
rstPedigree("RingNumber").Value = ss
rstPedigree.Update
rstPedigree.Close
Set rstPedigree = Nothing
End If
Next ii
GetAVK = Round((100 / x) * (x - i), 3)

Exit Function


On Error GoTo 0
Exit Function

TestButton_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure TestButton_Click, line " & Erl & "."

End Function

 

[InFlight]

Yes, now i see why it is wrong, also it could be 8 or 9 generations. I was just trying things. I will probably not use that query, it is to big and slow. I also moved the AVK and Inbreeding to the Bird Table. Sometimes i do things to make it easy for me even if it is the wrong way of doing it.

 

[InFlight]

OMG what a lot of work you have done for me. How can i ever thank you. It is going to take some time to go through it and understand it but i will. Question What is the Pathkey and does the code work it out. Thank you so much. I hope you enjoyed doing it. It quickly show the inbreeding that has occurred. It will be a whole new approach to next years breeding season.

 

[Spam808]

MajP would you be welling to help me out are done with inflight. I would like to learn how to use access, and make sure my coding is correctly and my form will work properly.

 

[isladogs]

It is not bragging it is just a fact. If a ton a people can answer the question I tend not to bother with the thread. If it is like this and no one can answer it then I find it challenging. No one else on this forum was going to try to calculate COI and build a pedigree with a recursive call. Or at least I did not see anyone interested in trying.

The reality is that once someone shows an interest in solving a topic, others tend to leave them to it.
I have no doubt that this was a difficult topic and it was one that didn't interest me so I left it alone.
I am quite sure that others could have solved it as well.

 

[isladogs]

MajP
I'm not showing any hostility here.

Just please just stop stating how wonderful you are at regular intervals.
Several people have picked you up on this in the past.

You are clearly a capable user of Access but so are many other forum members.
Just let others decide how good you are.

 

[Spam808]

@MajP,

I do have a difficult project that is similar to what you are on working, that is why I approach you.