>>>I have a transaction table A (brand, prod_id, prod_age, in, out). My user needs a delivery pick-up list report. The pick-up requirement is usually based on brand. For example, brand A, I need 300, brand B, I need 200, etc., '300' can be 20 from prod_1, 50 from prod_2, etc. The oldest product should be pick-up first for each brand. The requirement is stored in another table B (brand, req_qty)
>>
>>select a.brand, a.prod_id, b.req_qty, a.in, a.out ;
>> from a table_A, b table_B ;
>> where (a.brand = b.brand) order by 1

>No, it is not right. I need a result that shows
>
>Brand_A Prod1_1 20 Prod1_5 50 Prod1_6 80 ... Subtot 300
>Brand_B Prod2_3 46 Prod2_9 23 ... Subtot 200

shihchau,

select a.brand, a.prod_id, b.req_qty ;
from a table_A, b table_B ;
where (a.brand = b.brand) order by 1 into cursor temp
brow

the rsult may look like these :

Brand_A Prod1_5 50
Brand_A Prod1_5 50
...
Brand_B Prod2_3 46
Brand_B Prod2_9 23

try to see how to use sum()
keep on experimenting with sql until you get the results
you want. refer to the vfp online help on sql than book.

good luck.