Mouse Breeding Colony Template

[Lab]

I have a growing colony of genetically altered mice. I am trying to
set up a database in Access to keep track of the mice, their parents,
their pedigrees, and to generate certain end-of-year reports on the
fate of the mice.

Has anyone in this group created such a database for this specific
purpose? I am interested in getting a template or example that might
help me to learn a good way to organize the tables and relationships
as well as how to structure specific queries for specific outputs.

My Initial Strategy is to have a tables for ....
1. Individual Mice--keeps unique ID number, sex, genotype, date of
birth, BREEDING PAIR
2. Breeding Pairs--keeps M and F parent ID's and date they were put
together
3. Litters--keeps size of litters, date of birth, and size at weaning
(separating from parents), and wean date
4. List of varieties of mice (categories)--just so we can add types
of mice easily in the future when we make or get them (makes pull down
menu entry of mouse type easy).
5. List of rooms animals might be in--again, just a list of what room
the mice are in--may change occasionally so we can update pulldown
menus easily

Any templates or general advice would be helpful.

Craig

 

[Joan Wild]

I am not aware of a template that specific. I have done a tree breeding
database, but that is quite different since selfing and polycrosses are
involved.

There have certainly been others in the past asking about setting up a
database to track dog breeding/pedigrees. As long as cloning isn't part of
the picture, I would think following the dog samples would suit. A search
at google groups yielded the following. You may find some pointers there.

http://groups.google.com.my/groups?...981&as_maxd=2&as_maxm=2&as_maxy=2007&safe=off

Also check out http://www.allenbrowne.com/ser-06.html as that addresses how
to get the pedigree.

 

[Lab]

Dear Joan,

Thank you very much for nudging me in the right direction. With some
trial and error, I was able to figure out how to make a pedigree using
self-joins like the example.

Now I'm learning to use queries to limit my data entry. I have a
table for all Mice and a table for each breeding pair I have going. I
was hoping to enter the breeding pair once with mom and dad ID's.
Then when entering their offspring, I could just type in the unique ID
for the Breeder pair rather than both parents' ID's. The hope was
that this would allow me to easily keep track of reports like:
How many breeding pairs of a certain genotype do I have?
Pedigree of a given mouse.

In addition, I would like to generate a report showing all of the
litters produced by a particular Dam or Sire. This makes for a more
complex query than I had anticipated and I can't figure it out. It
basically has to look for every instance of a DamID in the Breeder
Pair Table, remember the BreederID, then look for all the mice in the
Mouse Table and sort them by date of birth. Some sort of count of
number of litters (mice born of this Dam on the same date) and count
the number of total offspring.

Any additional tips anyone could give me in setting up the tables
would be appreciated. I could give up on the Breeder table and just
enter the mom and dad in each indiv. record, but then I could never
generate a report that says how many breeding cages I have of each
genotype.

Craig

 

[Joan Wild]

Thank you very much for nudging me in the right direction. With some
trial and error, I was able to figure out how to make a pedigree using
self-joins like the example.

Now I'm learning to use queries to limit my data entry. I have a
table for all Mice and a table for each breeding pair I have going. I
was hoping to enter the breeding pair once with mom and dad ID's.
Then when entering their offspring, I could just type in the unique ID
for the Breeder pair rather than both parents' ID's. The hope was
that this would allow me to easily keep track of reports like:
How many breeding pairs of a certain genotype do I have?
Pedigree of a given mouse.

I don't see a problem with that. You may find there is other information
you want to track on a breeding pair.

Looking at your first post with the tables proposed:
tblMice--MouseID, gender, genotype, LitterID, MouseType, Room, Status
I removed date of birth as this really applies to the litter, not the mouse.
I assume you want to track the room the mouse is currently in. If you need
to track all the places it's been, you'd need a separate table with MouseID,
Room, DateIn, DateOut

tblBreedingPairs-- BreedingPairID, MaleParentID, FemaleParentID
I removed the 'date they were put together' as again, I would think this
applies to the litter; also you enter the pair once.

tblLitters-- LitterID, BreedingPairID, MatingDate, LitterSize, BirthDate,
WeanSize, WeanDate

lkpMouseVariety--MouseType
lkpRooms--Room
lkpStatus--Status

See www.jmwild.com/temporary.htm for the relationship diagram.

To mimic Allen's example of the pedigree, replace his tblHorses with a query
of your mice.
SELECT tblMice.MouseID, tblBreedingPairs.MaleParent,
tblBreedingPairs.FemaleParent
FROM (tblBreedingPairs INNER JOIN tblLitters ON
tblBreedingPairs.BreedingPairID = tblLitters.BreedingPairID) INNER JOIN
tblMice ON tblLitters.LitterID = tblMice.LitterID;

Use that as the basis of your pedigree query.

In addition, I would like to generate a report showing all of the
litters produced by a particular Dam or Sire. This makes for a more
complex query than I had anticipated and I can't figure it out. It
basically has to look for every instance of a DamID in the Breeder
Pair Table, remember the BreederID, then look for all the mice in the
Mouse Table and sort them by date of birth. Some sort of count of
number of litters (mice born of this Dam on the same date) and count
the number of total offspring.

SELECT tblBreedingPairs.FemaleParent, tblLitters.Birthdate,
tblLitters.LitterSize
FROM tblBreedingPairs INNER JOIN tblLitters ON
tblBreedingPairs.BreedingPairID = tblLitters.BreedingPairID
ORDER BY tblBreedingPairs.FemaleParent, tblLitters.Birthdate;

# litters by female
SELECT tblBreedingPairs.FemaleParent, Count(tblLitters.LitterID) AS
CountOfLitterID
FROM tblBreedingPairs INNER JOIN tblLitters ON
tblBreedingPairs.BreedingPairID = tblLitters.BreedingPairID
GROUP BY tblBreedingPairs.FemaleParent;

or you can use Sum(LitterSize) to get the total number of offspring.