Update table field with number of rows from a query

[Nertskull]

I have a query that returns something like this

ParentID	Father	Mother1	Mother2	Mother3
2	1112	1305	1308
3	1118	1301
7	1118	1301

Its keeping track of breeding mice that I use for work. The Father/Mother1,2,3 are the parents. So there are two male mice (1112 and 1118) and three female mothers (1305,1301,1308).

The ParentID refers to a liter born to those specific parents. So ParentID 2 is a litter, ParentID 3 is a different litter, and so forth.

What I need to track, is how many times each parent has given birth to a litter. So each mouse has a field in a separate table labeled "LitterNum". I want to update that number from the fields above.

So, in this example, it would be this

MouseNumber	LitterNum
1112	1
1118	2
1301	2
1305	1
1308

Thats what I want it to update it to. Right now, the LitterNum colum for all mice is "0". The values will keep changing though. I'll have another litter with new parents later. Maybe I'll pair 1118 with 1305 and need to update them to "3" and "2" litters, respectively, later.

I have a table of "mice" which includes the mice number and the litter num. Then I have a table of "parents" which has the parentID. So how can I use that parent ID in the first table, to update the litternum field in the mice table?



Basically I need to count records/rows in that query according to the Mouse number. Then update the number of records per mouse number into my litter num field.

I hope that made sense what I'm trying to do. It was kind of hard for me to explain. Thanks for the help everyone.

[alansidman]

Your table is not normalized. You should not have three fields for mothers but only one. Look at this explanation on data base set up and normalization.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

[Nertskull]

No, I'm pretty sure its right. I have to have three fields for mothers. Because you sometimes pair 1 male with 2 or 3 females at a time. So each one of those mothers is a different animal part of the "pairing". You end up not knowing exactly which pup cam from which mother. But thats the standard in this field, you just assign each mother having given birth once. Even though its possible the entire litter came from only one mother.

But either way, the problem is the same. Even if I take out the other 2 mothers. I still need a way to update number of litters to the father and the one mother.