>Thats a difficult One. (Pls read it completely to understand my problem. )
>
>I have made a project for a export company's stores/warehouse, they have codified each and every order, and import the goods related with each order....
>
>the chain is like that:-
>
>main order -> Multiple Invoices of imported goods
>Each & every Invoice -> multiple Containers
>Each & every container -> multiple goods
>
>I have following files :-
>
>main.dbf - The main Database file
>Fields - Code, Description
>
>Invoice.dbf - Invoice File
>Fields - code, InvNo, ContCod
>
>Container.dbf - Container File
>Fields - ContCode, ContType, Itemcod
>
>Items.dbf - Items file
>Fields - ContCod, ItemCode, Description, QtyRcvd
>
>The relation is like that
>
>Main -> Invoice -> container -> items
>
>I want to generate a cross tabulate report from these four files :-
>
>On page Header :-
>
>Main order No. Main Order Description
>
>on group header :-
>
>all the Invoices received related with that order, say I have received 3 invoices, so
>
>inv1 inv2 inv3
>
>in details band
>
>Unique items from items file -> in front of it, total qty received for that particular item, below that particular invoice....
>
>Presently, I have made a very long routine to get to this output, but it is very lengthy, complicated, difficult to modify/debug...
>
>Can somebody please suggest me a way to do this thing, through sql(s)...

|SELECT main.code, invoice.invno, items.description, items.itemcode, qty AS |sum(items.qtyrcvd) ;
| FROM main, invoice, container, items ;
| WHERE main.code = invoice.code AND ;
| invoice.contcod = container.contcode AND ;
| container.itemcode = items.itemcode ;
| ORDER BY code, invno, itemcode;
| GROUP BY itemcode

Then use a function to get each listing of invoices for each order and call it from the main header band: GetInvoice(code)
|FUNCTION GetInvoice(tcCode)
| LOCAL lcReturn, lcAlias
| lcReturn = ""
| lcAlias = ALIAS()
| SELECT InvNo FROM invoice WHERE code = tcCode INTO CURSOR acTemp
| SCAN
| lcReturn = lcReturn + InvNo + " "
| ENDSCAN
| USE IN acTemp
| IF !EMPTY(lcAlias)
| SELECT (lcAlias)
| ENDIF
|RETURN lcReturn

I think that will work, unless I'm misunderstanding your data structure