Linking database fields for automatic update of information

[callumas]

Morning everyone

I have made up a database to record generations of birds; along with others I have the following fields:

Ring number (primary key)
Sex
Year
Colour

Data entry via a form view.

For new entries I pick up from a combo box the parents, what I need to do now is create a relationship between 1, 3&4 and 2, 5&6 so that the data will automatically slot in the relevant fields.

1 Parent [censored]
2 Parent Hen
3 Grand Parent [censored] C/S
4 Grand Parent Hen C/S
5 Grand Parent [censored] H/S
6 Grand Parent Hen H/S

would I do it via a query.’ And would one cover relevant generations

Thanks for any help

Norma

[Scott Price]

Morning everyone

I have made up a database to record generations of birds; along with others I have the following fields:

Ring number (primary key)
Sex
Year
Colour

Data entry via a form view.

For new entries I pick up from a combo box the parents, what I need to do now is create a relationship between 1, 3&4 and 2, 5&6 so that the data will automatically slot in the relevant fields.

1 Parent [censored]
2 Parent Hen
3 Grand Parent [censored] C/S
4 Grand Parent Hen C/S
5 Grand Parent [censored] H/S
6 Grand Parent Hen H/S

would I do it via a query.’ And would one cover relevant generations

Thanks for any help

Norma

The following link is to Allen Browne's website where he covers how to link a table to itself. The subject he is using is horse breeding/pedigrees, but should be adaptable to your situation as well. http://allenbrowne.com/ser-06.html

You might need to adjust the structure of your table, as his method does away with the need for Grand Parent fields.

The basic idea is to drag three copies of the same table into one query in design view. Rename two of them, then create the relationships by dragging the FatherID and MotherID fields to their respective locations. This can be repeated for as many layers of ancestors as desired.

Regards,
Scott

[callumas]

Hi Scott

Just had a look at what you have said and Allen Browne's example looks just what I need.

thanks for your help and I'm sure I'll be back.

Norma

[Scott Price]

Hi Scott

Just had a look at what you have said and Allen Browne's example looks just what I need.

thanks for your help and I'm sure I'll be back.

Norma

You're welcome, and welcome to the Scripts!

Regards,
Scott