>I have the following join SQL:
>
>SELECT Apdetl.*, SUM(Appay.amount);
> FROM acctpro!apdetl LEFT OUTER JOIN acctpro!appay ;
> ON Apdetl.apdetlid = Appay.apdetlid;
> WHERE Apdetl.apmasid = 1.0000;
> ORDER BY Apdetl.ivcdate, Apdetl.ivcnum
>
>My testing file has two records that apdetl.apmasid=1. When I put the SUM statement in,
>I get only the first record. When I take the SUM out, I get both records. The APPAY table
>is currently empty.
>
>How do I get both records to display and eventually the sum of the amount field
>from APPAY that goes with the APDETL record by apdetlid?
As Trey asid, you need a GROUP BY clause so VFP knows what data the aggregate should be based on.
But remember to specify every field in the Apdetl table in the GROUP BY clause. Otherwise, you will get the aggregate of the Amount field and the correct data from the fields in the GROUP BY clause and the then data from the last record (physically) in the Appdetl table that matches the group criteria. This last data will probably be incorrect.
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao