Thanks!

Michelle


>I have 2 suggestions for you:
>
>1) You create a UDF that return the next index # for the current master #
>

CREATE CURSOR Temp (Master I, NextIndex I)
>SELECT Master, MyUDF(Master) AS Index, Item FROM MyTable
>USE IN Temp
>
>FUNCTION MyUDF (lnMaster)
>LOCAL lnAlias, lnIndex
>lnAlias = Alias()
>SELECT Temp
>LOCATE FOR Master = lnMaster
>
>IF Found()
>   REPLACE NextIndex WITH NextIndex + 1
>ELSE
>   INSERT INTO Temp VALUES (lnMaster, 1)
>ENDIF
>
>lnAlias = NextIndex
>SELECT (lnAlias)
>RETURN lnIndex

>
>2) You create a placeholder in the SELECT-SQL and you do a scan to fill it
>

SELECT Master, 0 AS Index, Item FROM MyTable INTO TABLE Temp ORDER BY 1
>lnI = 0
>lnMaster = Master
>SCAN
>   IF lnMaster = Master
>      lnI = lnI + 1
>   ELSE
>      lnMaster = Master
>      lnI = 1
>   ENDIF
>   REPLACE Index WITH lnI
>ENDSCAN

Note that the second option needs a physical table on disk. There is a way to open a cursor created with a SELECT-SQL as read-write, but I don't remember it by hear (somthing like USE AGAIN IN 0 ALIAS)
>
>HTH
>
>>Say I have a table that looks like this:
>>
>>

>>
>>   master     item
>>     1        apple
>>     1        banana
>>     1        pear
>>     2        tomato
>>     2        apricot
>>     3        squash
>>     4        turnip
>>     4        orange
>>
>>

>>
>>I want to get this:
>>
>>

>>
>>   master  #   item
>>     1     1   apple
>>     1     2   banana
>>     1     3   pear
>>     2     1   tomato
>>     2     2   apricot
>>     3     1   squash
>>     4     1   turnip
>>     4     2   orange
>>
>>

>>
>>Is there a way to do this with SQL? It seems like there should be, but it's eluding me. I'm trying to number the items so I can combine a bunch of records into one in a different SQL so I can print the labels I was asking about earlier.