Just wrap all fields into MAX() function, e.g. Max(InvHead.OrderID) as OrderID, InvHead.InvoiceNo, Max(InvHead.InvoiceDate) as InvoiceDate, ...
etc.
>Can people help on how to change this query to run in VFP9 from VFP7?
>
>Group by is invalid is the error
>
>SELECT Invhead.orderid, Invhead.invoiceno, Invhead.invoicedate,;
>Customer.Code as Code, Invhead.freight, Invhead.brokerage, ;
>Ordhead.entered, Customer.name AS custname, Ordhead.location, ;
>Invprod.invoiceqty, SUM(invoiceqty*sellprice) as Value, ;
>SUM(invprod.invoiceqty*ordprod.sellprice)*(InvHead.Discount/100) as Discount, ;
>(SUM(invprod.invoiceqty*ordprod.sellprice)-(SUM(invprod.invoiceqty*ordprod.sellprice)*(InvHead.Discount/100)) + InvHead.Freight)*(InvHead.Tax/100) as Tax ;
>FROM invhead, invprod, ordhead, ordprod, customer ;
>WHERE Invhead.orderid = Ordhead.orderid ;
>AND Invhead.invoiceno = Invprod.invoiceno ;
>AND Ordhead.customerid = Customer.customerid ;
>AND Ordhead.orderid = Ordprod.orderid ;
>AND Invprod.productid = Ordprod.productid ;
>GROUP BY Invhead.invoiceno ;
>INTO CURSOR salejour
>
>
>Needless to say, adding all fields to the group by gives more records...
>
>Help!
>
>TIA,
>Mike
If it's not broken, fix it until it is.
My Blog