[Babase] (hopefully easy) request for Karl

Karl O. Pinc babase@www.eco.princeton.edu
Thu, 10 Mar 2005 21:02:33 +0000


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