Question Setting up relationships for pedigree project

[Pedigreeman]

Dear all,

Firstly, I am very new to MS Access and indeed the forum (hello!), so I am very pleased to have stumbled upon this website and hope that some of you might be patient enough to help me out.

The situation: I breed birds as a hobby, and I am trying to build a pedigree database to keep track of the breeding history (excel has been fantastic, but I think MS Access will be even better).

My question:
I have created two tables. Firstly 'T_stocklist' which contains all data for all birds currently held. Secondly 'T_eggdata' which will contain all new record entries for egg and chick data for those birds already recorded in the stocklist.

T_stocklist contains the field headings:

*Ring number [text]
Species
Colour
Sex

T_eggdata contains the field headings:

*Egg ID [autonumber]
Laid date
Hatch date
Mother ring number
Mother species
Mother colour
Father ring number
Father species
Father colour

*= primary key

In table, 'T_eggdata' I want to be able to enter the mothers ring number manually, and then for it to fill in the other fields automatically based on the info from the first table, 'T_stocklist'. I have currently made a one to many relationship between '*Ring number', and 'Mother ring number', but this '*Ring number' should also be linked to 'Father ring number' as he will also be mentioned in 'T_stocklist' in the same field. Access wont let me do this with enforced referential integrity? Should I split the stocklist into two tables, one with female birds and one with male birds?

I am really looking for any tips, advice, shortcuts, better ways of doing things or to be pointed in the right direction at this stage, and would very much appreciate the help.

 

[JANR]

Look into self join, here is an explenation :http://allenbrowne.com/ser-06.html

JR

 

[gemma-the-husky]

I wouldn't think of "eggs" as an entity - an egg is just a bird, really isn't it. Think of the birds only, surely.

maybe this sort of structure, will work

[as an aside - I think you could do this either with or without recording the breedings/clutches details. It depends how important the circumstances of the brood/hatching are, in assessing the quality of the birds. if the breeding details are important, then maybe this sort of thing.

bird table (id, sex, name, clutchid, etc etc)

clutch table(clutchid, fatherbirdid, motherbirdid, other details relating to the nesting)

in most bird species, you will probably have several birds from the same clutch of eggs. If the birth order is important, then you probably need a birth order sub table, related to the clutch table, so that you can identify that a given bird was the first hatched from the clutch, etc.

You need to consider how you will deal with imperfect data, I think, also. eg If you buy stock in, you may not know all these details.

I expect bird breeding is similar to any livestock - why not see if there is any suitable templates in either normal farming applications, or something like dog or rabbit breeding.



The other thing is I wouldnt use the ring number as a PK. Have it as an indexed attribute in the bird table, by all means, but you are certainly going to need to record chick details before you have a ring number. And no doubt at some point you will have a bird that has to be re-numbered. Have a separate autonumber ID field, in the bird table. Then the ring numbers can be changed without affecting any other data.

 

[Pedigreeman]

Many thanks Gemma,

Good point about PK - I will need to put in information before a ring number is assigned.

The breeding details are important, I intend to add more fields than I have mentioned here (such as incubation time) but want to better understand the software first. I want to look for patterns in the breeding data (why some eggs don't hatch for example (are they too heavy/laid too early/incubated for too long etc.). I think an egg is an entity if it never becomes a bird? I still want details on the infertile or dead eggs.

So having set up my two tables, how do I make them talk to each other? For example, an egg has been laid by bird1 who is paired up with bird15 (this has been noted in a field in the first table). Excited by the new egg, I rush to my computer and click on the second table and add a 'laid date' at which point it creates an autonumber for the eggID (number '1', as it is the first egg of the year). I manually write in the mothers ring number, now how do I get it to enter, for example, the fathers details, or more details about the mother, found on table 1, automatically? Is that via a query or do tables autofill when they are correctly linked up with relationships?

 

[gemma-the-husky]

all you need to record in the egg table (do they only do one at a time?) is the ID of the mother and the father - and you can get these via a drop down, or any other easy way.
(and as I said - I would use an ID, not the ring number)

the videa is not to have write numbersi n manually - you need never see the ID number.
eg show a form of all the breeding mothers (with ring numbers!), a form of the breeding fathers, select one of each, and have a button that picks up the underlying ID number of the mother and father, and stores them in your egg table (you never actually need to see the actual number that is stored - its just there to link the tables together)

you then pick up all the other details relating to the mother/father, in a query, as and when you need them. You do not need to store them with the chick.

...
NOW, if each egg is considered as a (unborn) bird, then maybe you don't need an egg table at all - you could just put the putative bird straight into the bird table, with a "status" setting of unhatched. Then when it hatches, just record the new status. Otherwsie you then have ot transfer the egg entry into the bird table.


The best thing is just to try it all and see. Look at forms and subforms. Look at combo boxes and list boxes. It won't be perfectly right the first time. But build on what you have got gradually, and you will get it right in the end

 

[Pedigreeman]

Thanks Dave. So BirdID would correspond to each egg and its respective bird if the egg hatches. I will try a few things out.