What exactly is the problem - get the record with the biggest quantity and its details?
If so, here are two links to help you out
Including an Aggregated Column's Related ValuesIncluding an Aggregated Column's Related Values - Part 2>Hi All
>
>I have a next problem to solve. I know I can do via xBase, but I'm intersted to do it in SQL
>
>given is a set of data lets say "Material" that has to forced to be dealed with. Same material exists several times. We have an unique index to distinct the records
>
>*basic set
>CREATE CURSOR curBase (P_IX I,iMat I, nQty N(12,3))
>TEXT TO lcTemp NOSHOW
>P_IX,iMat,nQty
>1,1,2
>2,1,3
>3,1,4
>4,2,0.2
>5,2,20
>6,2,8
>7,2,22
>ENDTEXT
>
>STRTOFILE(lcTemp,'Help.csv')
>APPEND FROM HELP.CSV TYPE CSV
>DELETE FILE HELP.CSV
>
>
>Now the user wants to deal with the sums of the material,
>
>*summation set
>SELECT;
> iMat, SUM(nQty) AS nQty;
> FROM curBase;
> INTO curSum READWRITE;
> GROUP BY 1
>
>
>Now the user likes to handle the amount or less
>
>*one altered
>REPLACE nQty WITH nQty-5
>
>
>[tricky part]
>lets say this material that has to be handed out from a storage. I to hand out the material from the store (we don't care about fine details) but we need to notify for wich record of the base set we hand out
>
>*result cursor
>CREATE CURSOR curTarget (P_IX I,iBase I,iMat I, nQty N(12,3))
>*iBase points to curBase.P_IX
>
>
>I have the wear down the basic set biggest qty first (better would be best fitting)
>
>so for the given example (Material 1 qty 4) the set need to be
>
>*Result set
>TEXT TO lcTemp NOSHOW
>P_IX,iBase,iMat,nQty
>1,3,1,4
>2,7,2,22
>3,5,2,21
>4,6,2,8
>5,4,1,0.2
>ENDTEXT
>
>
>if I repeat the procsss for qty=4 on Material 1 again it would be
>
>
>*summation set
>REPLACE nQty WITH 0 ALL
>LOCATE
>REPLACE nQty WITH 4
>
>*input into result set curTarget neede here
>
>*Result set now (old and new data)
>TEXT TO lcTemp NOSHOW
>P_IX,iMat,nQty
>1,3,1,4
>2,7,2,22
>3,5,2,21
>4,6,2,8
>5,4,1,0.2
>6,2,1,3
>7,1,1,1
>ENDTEXT
>
>
>Any idea to do this via SQL INPUT?
>
>TIA
>
>Agnes
If it's not broken, fix it until it is.
My Blog