If you need to include more fields from Orders table into your final result, you can not do this in one select statement in VFP8. In VFP9 you can use derived tables to get your result easily.

>Hello all,
>
>I have two tables and want to write a single SQL statement to return results. The two tables are Order and Shipment. The Order table has OrderID as its PK and the Shipment table has OrderID as a FK. The Shipment table also has a ShipDate column and a ShipAmt column.
>
>What I would like to do is have a result set that shows the OrderID and two columns that are SUM's of ShipAmt. The first SUM should be the sum of ShipAmt for ShipDate less than a FromDate parameter and the second SUM should be the sum of ShipAmt for ShipDate between the FromDate parameter and a ToDate parameter.
>
>So, in summary, I would like to list each order in the order table with columns showing total shipped before a from date parameter and total shipped in the period defined by the from date parameter and a to date parameter.
>
>Any help is appreciated.
>
>TIA