Calculating a date range

[unicorn]

I have a large database of sheep with lamb id, a mother id and the year of birth for each lamb normally a sheep will have a litter every year but some years the mother has no lamb and is therefore missing from the data I want to find a way to calculate the range of years a mother is fertile attached is a small part of the database


Thanks

[unicorn]

Thanks for taking the time to read this if you did I have now figured out a way to do it if there is an easier way then it would be great if anyone was willing to share it
I made a query with the mothers id (DamID) and also year grouped by min and year grouped by max I think I have to now export this to excel to subtract one year from the other is this true or can I also do that in access?
Thanks in advance

[jzwp11]

I did not see the lambID or motherID fields in the example DB you posted. By the way can't a lamb eventually be a mother and have a litter of her own? Do you have a table in your main database that handles the mother/father/offspring relationship?

[unicorn]

hi jzwp11
thanks for your reply sorry I wasn't very clear with my terms the lamb id is SheepID in the data and mother id is called GeneticDamID. I am building a multigenerational database that goes back 4 generations and so yes in my main database the parents are all linked I am doing all the calculations in a single table before linking GeneticDamID in one table to sheep ID in a copy of the original table (and the same for SireID, grandsire etc...) but that's probably more information than you need

[jzwp11]

I still didn't see a sheepID, so I would have to assume that SireID is the offspring's ID. I also have to assume that the Date of Birth field refers to the offspring rather than the mother. If my assumptions are correct, a query like this will get the date of birth for the earliest offspring and the date of birth for the most recent offspring

query name: qryDOBRanges
SELECT [example births].GENETICDAMID, Min([example births].DATEOFBIRTH) AS MinOfDATEOFBIRTH, Max([example births].DATEOFBIRTH) AS MaxOfDATEOFBIRTH
FROM [example births]
GROUP BY [example births].GENETICDAMID;


You can use the above query in another query that calculates the range in years.

SELECT qryDOBRanges.GENETICDAMID, qryDOBRanges.MinOfDATEOFBIRTH, qryDOBRanges.MaxOfDATEOFBIRTH, datediff("m",MinOfdateOfBirth,maxOFdateOFBIRTH)/12 as YearsFertile
FROM qryDOBRanges;


Now as to my earlier question about how you handle the relationship between the sheep (mother/father/offspring), all sheep, no matter whether they are father, mother or an offspring belong in 1 table and then you would use another table to show the relationship of one sheep to another

tblSheep
-pkSheepID primary key, autonumber
-DOB
other fields


tblRelatedSheep
-pkRelatedSheepID primary key, autonumber
-fkPSheepID foreign key to tblSheep (indicates the parent)
-fkSSheepID foreign key to tblSheep (indicates the child)


As an example, let's say that an offspring has a pkSheepID=555, it's mother has a pkSheepID =200 and its father has a pkSheepID =100
There would be two records in tblRelatedSheep as follows:

pkRelatedSheepID|fkPSheepID|fkSSheepID
1|200|555
2|100|555

It was unclear to me whether this is how you have your table set up.

[unicorn]

thanks again this is method is working well and thanks also for the advise on generational table setup - the way you describe is how i have set it up
best wishes and smiles sent your way

[jzwp11]

You're welcome. Good luck with the project & the sheep.