Pedigree query for Registration Database

[dvanwagnen]

Howdy,

I've been away from Access for a few years, just trying to get my legs back under me.

I have a challenge to create a relational database of horses. The primary hurdle I can't get over is this:

Primary info, each record will have unique registration number (key), a field for sire (father) and dam (mother) for each horse. These fields will be horses also in the database.

I need to be able to query the pedigree(herita ge if you will) back 5 generations. I'm stuck as to how to do that.

IE: Great/Great Grandad(G5)
Great Grandad (G4) " Grandma
Grandad(dads)(G 3) Great Grandma " Grandad
DAD(G2) Grandma(dads) Great Grandad " Grandma
Great Grandad " Grandad
" Grandma
Me (Gen 1) " Grandad
" Grandma

MOM(G2) Great/Great Grandad(G5)
Granddad(moms)( G3) Great Grandad (G4) " Grandma
Grandma(moms) Great Grandma " Grandad
Great Grandad " Grandma
Great Grandma " Grandad
" Grandma
" Grandad
" Grandma

I can do the form layouts, prints, etc, but having trouble setting up the query to get all this for the original Gen 1.

Any assistance I can find is appreciated.

[dvanwagnen]

Sorry - My layout didn't work out the way I planned.

Basically, I need the equivalent of a geneology tree for one horse going back 5 generations.

Thanks
Dan

[MMcCarthy]

As i understand it you have a table with two foreign keys set to its own primary key. In a query you will have to have three instances of the table.

tblHorses
tblHorses_1 'First foreign key joined to primary key of first instance
tblHorses_2 'Second foreign key joined to primary key of first instance

This should allow you to query all decendants

Howdy,

I've been away from Access for a few years, just trying to get my legs back under me.

I have a challenge to create a relational database of horses. The primary hurdle I can't get over is this:

Primary info, each record will have unique registration number (key), a field for sire (father) and dam (mother) for each horse. These fields will be horses also in the database.

I need to be able to query the pedigree(herita ge if you will) back 5 generations. I'm stuck as to how to do that.

IE: Great/Great Grandad(G5)
Great Grandad (G4) " Grandma
Grandad(dads)(G 3) Great Grandma " Grandad
DAD(G2) Grandma(dads) Great Grandad " Grandma
Great Grandad " Grandad
" Grandma
Me (Gen 1) " Grandad
" Grandma

MOM(G2) Great/Great Grandad(G5)
Granddad(moms)( G3) Great Grandad (G4) " Grandma
Grandma(moms) Great Grandma " Grandad
Great Grandad " Grandma
Great Grandma " Grandad
" Grandma
" Grandad
" Grandma

I can do the form layouts, prints, etc, but having trouble setting up the query to get all this for the original Gen 1.

Any assistance I can find is appreciated.

[dvanwagnen]

As i understand it you have a table with two foreign keys set to its own primary key. In a query you will have to have three instances of the table.

tblHorses
tblHorses_1 'First foreign key joined to primary key of first instance
tblHorses_2 'Second foreign key joined to primary key of first instance

This should allow you to query all decendants

Thanks - I'll see if that makes sense to me and I can get it to work.
Dan VW

[babrees]

I have a challenge to create a relational database of horses. The primary hurdle I can't get over is this:

Primary info, each record will have unique registration number (key), a field for sire (father) and dam (mother) for each horse. These fields will be horses also in the database.

I need to be able to query the pedigree(herita ge if you will) back 5 generations. I'm stuck as to how to do that.

IE: Great/Great Grandad(G5)
Great Grandad (G4) " Grandma
Grandad(dads)(G 3) Great Grandma " Grandad
DAD(G2) Grandma(dads) Great Grandad " Grandma
Great Grandad " Grandad
" Grandma
Me (Gen 1) " Grandad
" Grandma

MOM(G2) Great/Great Grandad(G5)
Granddad(moms)( G3) Great Grandad (G4) " Grandma
Grandma(moms) Great Grandma " Grandad
Great Grandad " Grandma
Great Grandma " Grandad
" Grandma
" Grandad
" Grandma

Hi

I've only just come across your post. Did you manage to do this ok? I have a dog pedigree database that is the same as this. if you want to see how it's done give me a shout.

Jill

[lizs]

Hi

I've only just come across your post. Did you manage to do this ok? I have a dog pedigree database that is the same as this. if you want to see how it's done give me a shout.

Jill

Jill,

I am just trying to set up a 5 generation Connemara Pony database and I would appreciate any guidance you can give me.

Thanks

[rogers803]

Hi

I've only just come across your post. Did you manage to do this ok? I have a dog pedigree database that is the same as this. if you want to see how it's done give me a shout.

Jill

Jill,

I am trying to set up a dog pedigree database and would love to see how you did it.

Alan

[cupcake]

Jill,

I am trying to set up a Quarter Horse database and I would like to make pedigrees but I got a little stuck.

Can you help me on my way?

Thanks

[slaterdav]

Hi

I've only just come across your post. Did you manage to do this ok? I have a dog pedigree database that is the same as this. if you want to see how it's done give me a shout.

Jill

Jill,

or anyone who got a copy of Jill's database. I am stuggling to set up a database for my small cattle farm. Could I look at how you did it with the dog's.

Any help would be appreciated.

Dave

[stlduffer]

Jill,

or anyone who got a copy of Jill's database. I am stuggling to set up a database for my small cattle farm. Could I look at how you did it with the dog's.

Any help would be appreciated.

Dave

Same goes for me. If Jill or someone can pass along Jill's database, that would be awesome. I'm trying to set up something for our Border Collies. Don't want to end up putting in a ton of info and then finding out I didn't set things up right to begin with.

Thanks!
Chris

[youmike]

It's quite possible to do all of this in a custom Access database, but I think that for it to work efficiently, you'll end up needing quite extensive coding to build some sort of temporary table with all the generation info. The thought of trying to do this using nested queries just doesn't sound appealing.

As an alternative, why don't you consider using one of the many freeware family tree programs which you can find on the internet? Try a Google search for "family tree programs". I say this because designing the queries is only the beginning - the design of a report to display the generations intelligibly will probably present its own challenges.

[Smitty099099]

The GDI(Get Dog Info) Sub

Public Sub GDI(ByVal DN As String)
If DN = "" Then
Exit Sub
End If
strSQL = " SELECT * FROM Dogs WHERE [Name] " & " Like '%" & DN & "%'"
'//
Try
Cmd = New OleDbCommand
If Conn.State = ConnectionState .Closed Then Conn.Open()
Cmd.Connection = Conn
Cmd.CommandText = strSQL
Dim DR As OleDbDataReader = Cmd.ExecuteRead er
While DR.Read
PK = DR.Item("AutoId ").ToString
txtFullname.Tex t = DR.Item("Name") .ToString
txtNickname.Tex t = DR.Item("CallNa me").ToString
cmbGender.Text = DR.Item("Gender ").ToString
txtDOB.Text = DR.Item("DOB"). ToString
txtFather.Text = DR.Item("Father ").ToString
txtMother.Text = DR.Item("Mother ").ToString
txtAKC.Text = DR.Item("AKC"). ToString
txtOFA.Text = DR.Item("OFA"). ToString
txtADRK.Text = DR.Item("ADRK") .ToString
txtAwards.Text = DR.Item("Awards ").ToString
If DR.Item("Pic"). ToString <> "" Then
picData.ImageLo cation = strPathImages & DR.Item("Pic"). ToString
picData.Load()
Else
picData.ImageLo cation = strPathImages & "Dog.jpg"
picData.Load()
End If
End While
DR.Close()
Catch ex As Exception
MessageBox.Show (ex.Message)
End Try
End Sub

The GMD(Get Mom Dad) Sub

Public Sub GMD(ByVal DN As String, tbDad As TextBox, tbMom As TextBox)
If DN = "" Then
Exit Sub
End If
strSQL = " SELECT Name, Father, Mother FROM Dogs WHERE [Name] " & " Like '%" & DN & "%'"
'//
Try
Cmd = New OleDbCommand
If Conn.State = ConnectionState .Closed Then Conn.Open()
Cmd.Connection = Conn
Cmd.CommandText = strSQL
Dim DR As OleDbDataReader = Cmd.ExecuteRead er
Dim DRMom As String = ""
Dim DRDad As String = ""
While DR.Read
DRDad = DR.Item("Father ").ToString
DRMom = DR.Item("Mother ").ToString
End While
DR.Close()
tbDad.Text = DRDad
tbMom.Text = DRMom
Catch ex As Exception
MessageBox.Show (ex.Message)
End Try
End Sub

[Micokin]

It's messy, but I understand it. It's great.