Results 1 to 3 of 3
  1. #1
    Nertskull is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    2

    Update table field with number of rows from a query

    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.

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,478
    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

  3. #3
    Nertskull is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    2
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 03-17-2012, 04:46 AM
  2. Replies: 0
    Last Post: 02-09-2012, 05:43 PM
  3. Replies: 1
    Last Post: 08-31-2011, 04:03 PM
  4. Replies: 4
    Last Post: 04-18-2011, 07:18 AM
  5. Update Field list in Table with Query
    By Scorpio11 in forum Queries
    Replies: 3
    Last Post: 07-16-2010, 01:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums