Question calculate inbreeding

[InFlight]

Cheers. I am having a look at isladogs ColourConverter to see if it will work for me.

 

[MajP]

Confused on where you plan to store and get the RingColour. You show that a pairing season would have a specific ring. A birds ring color comes from the season its parents where paired, not when the bird was paired. So in your example 5187 is green because it parent was paired in 2012 and 7480 LP is orange because that was its season. If you continue to track in the bird table "Season" for each bird then the following will work.

Your colour table has a season and a color. I will assume a birds ring color comes from its season in Tbl_Birds. If that is correct make this helper function. You pass in a ringno for any bird, it finds its season, and looks up the color code for that season.

Public Function GetRingColour(RingNo As String) As String
  'work with IDs instead of ring no
  Dim Season As Long
  Season = DLookup("Season", "Tbl_Birds", "RingNo = '" & RingNo & "'")
  GetRingColour = Nz(DLookup("RingColour", "Tbl_RingColour", "SeasonID = " & Season), 0)
End Function

Now change the RingColour field to short text and use Isladogs tool to store the hex value of the color instead.
Add two unbound controls on the form for the hen and ****. You can hide or delete the originals. Change these two unbound controls to rich text. It is in the Text Format property.

Add this helper function. You will pass in the ring no and it will find that birds color and wrap it with rich text tags

Public Function ColorText(RingNo As String) As String
  Dim ColorTag As String
  ColorTag = GetRingColour(RingNo)
  ColorText = "<div><font color=#" & ColorTag & ">" & RingNo & "</font></div>"
End Function

in those unbound text boxes set the control source to

=GetRingColour([hen])

=GetRingColour([****])

 

[InFlight]

The Colors are stored in the tbl_Birds and looked up from tbl_RingColour
I have a query PartnerShip_Season that gets the info.

 

[MajP]

From what it looks like the OnPaint is working. I did not try to validate the information.

However, you seem to be doubling up information again. A bird's season determines it ring color. You are storing that in two places and storing the color information twice. If I know the season I know the ring color. I would just store that in a color table. Then you link by season. You do not need to enter it a 200+ times, just the 1 time for each season.

I did not see a query linking colours to season. It would be simply
qry_Seasons_RingColour

SELECT tbl_Season.SeasonID, tbl_RingColour.RingColour
FROM tbl_Season INNER JOIN tbl_RingColour ON tbl_Season.SeasonColor = tbl_RingColour.RingColourID;

Now you have a query that returns the color code for each season.

I think the season information is in two places. It is in the bird table for each bird. I can also get it from the pairing table. Here are the helper functions whichever you decide. Both may be OK so you can handle purchased birds that you did not breed (no pairing).
Here is how you can get it from a birds ringno

Public Function GetBirdSeason(RingNo As String) As String
  'work with IDs instead of ring no
  GetBirdSeason = Nz(DLookup("Season", "Tbl_Birds", "RingNo = '" & RingNo & "'"), 0)
End Function

Public Function GetBirdSeason2(RingNo As String) As String
  'work with IDs instead of ring no
  Dim BirdID As Long
  Dim PairID As Long
  BirdID = GetID(RingNo)
  PairID = GetPairID(BirdID)
  GetBirdSeason2 = Nz(DLookup("Season", "Tbl_Bird_Partnerships", "Pair_ID = " & PairID), 0)
End Function

If you know the season you know the color. No need to store it in the bird table.

Public Function GetRingColour(RingNo As String) As Long
  'work with IDs instead of ring no
  Dim Season As Long
  Season = GetBirdSeason(RingNo)
  'Season = GetBirdSeaon2(ringNo)
  GetRingColour = Nz(DLookup("RingColour", "qry_Season_RingColour", "SeasonID = " & Season), 0)
End Function

with that said your on paint simply becomes
Hen.ForeColor = GetRingColour(Me.Hen)
****.ForeColor = GetRingColour(Me.****)

And you do not have to save color codes in the bird table.

 

[MajP]

Personally I would change the backcolor and not the forecolor. The only thing to add is if the backcolor is dark, change the forecolor to white. It is a lot easier to see and read.

 

[MajP]

Actually after playing with it, it was kind of quirky. The colors change unexpectedly. If that does not work for you than it is a toss up. Using the rich text is a little less work, but using conditional formatting may be more labor but less technical.

To do Conditional formatting you would go to the Hen field. Select Conditional formatting and select
Expression Is: GetBirdSeason([Hen]) = 2010
and pick formatting you want. You have to do that for each year. It is easy to just hit paste and change the year. Then go to the **** field and do the same changing
Expression Is: GetBirdSeason([****]) = 20XX

This will give you the best performance, but it is a little labor intensive because you are adding a condition for each year..

 

[InFlight]

Yes i do have a lot of doubling up. It might be the first thing i do. With the on paint there is a lot of screen flicker which is annoying. Will look at conditional formatting also. Thanks

 

[InFlight]

Would this slow things down

GetBirdSeason([Hen])=2011 Or GetBirdSeason([Hen])=2016 Or GetBirdSeason([Hen])=2022 Or GetBirdSeason([Hen])=2029 Or GetBirdSeason([Hen])=2035 Or GetBirdSeason([Hen])=2041

 

[MajP]

Here is a demo using conditional formatting except I did the conditions on the **** field not the hen field by accident. Colors are notional.

and CF

It would be some manual work but how far you really have to go? You have values to 2099 but by then will all be driving flying cars and this db will be in a chip embedded in our brains and birds would be replaced by pet robots.

 

[MajP]

If you did conditional formatting then you should be able to do
Expression IS: GetBirdSeason([Hen])=2011 Or GetBirdSeason([Hen])=2016 Or GetBirdSeason([Hen])=2022 Or GetBirdSeason([Hen])=2029

I would only go out as far as reasonable. 2022 maybe, I doubt you are running this db in 2029.

 

[InFlight]

Good idea. Every one i know is around my age. So no one will be using it.

 

[MajP]

I think conditional formatting would give you the best performance, although it is more labor intensive initially. To be even more efficient you could do away with the function calls and link Tbl_birds to tbl_partnership twice by **** and by Hen ringNo. You would just pull the tbl_birds in twice. Then using just sql you would have the season of both parent birds. Then you could use conditional formatting on those. SQL is almost always faster then a function call using a domain aggregate function.

 

[InFlight]

With 216 Birds it takes 6 seconds

 

[MajP]

Sometimes you have to weigh being a data purist versus ease and efficiency. This is lot easier if you just put the actual Ring Colour in Tbl_Birds. Not the "pure" solution but simplifies things. When you input a new bird you can default the ringcolour based on the season to help with data integrity. Then in the partnership query you bring in table birds twice. Once joined to female ringno and once to the male ringno. That will allow you to have a hen colour and **** color field in the query. Then you do simple conditional formatting on those field. If value is black turn it black, if green turn it green. I will send an update. This is fast, simple, easy to maintain.

 

[InFlight]

Then in the partnership query you bring in table birds twice. Once joined to female ringno and once to the male ringno. That will allow you to have a hen colour and **** color field in the query. Then you do simple conditional formatting on those field. If value is black turn it black, if green turn it green. I will send an update. This is fast, simple, easy to maintain.

Do you mean create a new query or use one that i already have.

 

[MajP]

If you actually store the color names in tbl_Birds then you could Update the query as follows and bring in the parents ring colours.

If you wanted to be really normalized instead of saving the colors in tbl_birds you could take the above query and then to each parent table join the season table by season and then join the colour table by color id.

Using the query I added two text boxes and placed them behind the **** and Hen fields. These text boxes are the fields for the parent colors. Take a look at the conditional formatting on those controls. If you do not like the back color then just apply conditional formatting on the **** and hen ring number fields. The conditional formatting is faster and simpler since you can reference the parent ring color without a function call.

One thing I added which I find is usually real helpful is the ability to click on the labels and sort the list. Like you can do in Outlook. The above is sorted by **** and you can see the sorted label is underlined.

Also there is an easier way to do the totals, you could save a lot of code. Take a look at the control source for the totals.

 

[InFlight]

Hi. I have just got home. Been away so only just read this. Will have a good look in the morning. But what i have seen in the data base it is exactly what i what.
Thanks so much.

 

[MajP]

I need to write up a review on this Treeview Control, because it works real well and is completely based on MSFORMS control so it is very portable.
https://www.jkp-ads.com/articles/treeview.asp

So when playing around with it I needed some recursive data and used the breeding data. Not sure if this view is useful. I loaded the tree so that each node is the pedigree. If you expand a bird node you get the parents, then if you expand the parents you get the grandparents....

I also did it the reverse way. Loading a bird then its children, then their children, ... You can add whatever text you want to the node.

So for example this is all the offspring for 5186, and then all of its offspring's children. I will send if you think it would be useful

 

[InFlight]

Yes that would be great. Especially when selecting birds for the next breeding season. I have written another database to run bird shows in NZ. A lot of bird clubs in NZ use it to run there shows. I have run 2 Nationals show with it. If you would like to have a look at it let me know. It uses the town distance code and use a back end and demo data base that they can learn on.
I did change the conditional Formatting. Using the ColorID instead of ColorName

 

[InFlight]

I do something similar to this manually in Excel and takes a lot off work. This working with 2097, 1 (4) of the first Glosters we got. At the moment only working on children, grand children and great grand children. She has 99 off spring.