[Babase] (hopefully easy) request for Karl

Susan Alberts babase@www.eco.princeton.edu
Thu, 10 Mar 2005 17:26:22 -0500


Thanks for getting back to us. What we are trying to do is get around 
the fact that we don't have a functional ranks file for male ranks. 
We need to know how many males occupied rank number one, how many 
occupied rank 2 etc, during the sum total of all conceptions. We 
would normally just get the rank of each male that was present in the 
group at the time of conception and count all 1's, all 2's, etc, but 
we can't do that becuase the data are not in babase in a clean form .

Susan

>On 03/10/2005 12:02:31 PM, Susan Alberts wrote:
>>Hi Karl,
>>
>>A quick question about SQL, we hope that this is an easy one for 
>>you to answer, it is quite simple in principle but we just don't 
>>know the sql command to use. We are querying babase babase and 
>>trying to construct a table that has a new field that is 
>>essentially an index or running count of cases.
>>
>>For instance, the first field in the output table is a list of males, e.g.,
>>
>>MaleA
>>MaleB
>>MaleC
>>MaleD
>>MaleA
>>MaleB
>>
>>The second field in the output table is the kid who they are 
>>potential fathers for, e.g.,
>>
>>KidA
>>KidA
>>KidA
>>KidA
>>KidB
>>KidB
>>
>>I.e., the first four males in the first field are males that were 
>>potential fathers for kidA, the next two males were potential 
>>fathers for KidB. Each Kid has multiple potential fathers.
>>
>>The third field, which we want to create, is a running count of the 
>>males for each kid, e.g.,
>>
>>1
>>2
>>3
>>4
>>1
>>2
>>
>>We don't want a simple count grouped by kid, we want an index or 
>>running count.
>>
>>Is there a simple way to do this that you can tell us about?
>
>There is no simple way to do this.  There are several more
>complicated ways, probably.  I'm not sure what will work in
>foxpro.  The question on my mind is why do you want to do this?
>The extra column adds no additional information, it assigns a
>number to the child, per potential father.  You could as easily
>assign a letter, or (ta da!) a unique sname -- as you already have
>in the child's sname.  But you already know this.  Usually something
>like your question is leading toward records being kept on the
>father-child pairing.  In this case you want a key that consists of
>the combination of the father and child ids, something like
>fathersname*childsname.  Or better, a table with columns like:
>uniquenumericid, fathersname, childsname, dataitem1, dataitem2, ....
>
>So, the question is where are you going with this?
>
>Karl <kop@meme.com>
>Free Software:  "You don't pay back, you pay forward."
>                 -- Robert A. Heinlein
>
>
>
>_______________________________________________
>Babase mailing list
>Babase@www.eco.princeton.edu
>http://www.eco.princeton.edu/mailman/listinfo/babase


-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan Alberts, Associate Professor 
Department of Biology, Duke University, Box 90338, Durham NC 27708 
phone 919-660-7272  fax 919-660-7293