>Dear Barbara,
>
>sCode = Supplier's product code
>sDetail = supplier's product description
>pCost = Product's Cost
>pDiscount = Product's discounted price
>sID = supplier's ID
>oMID = our Product code
>
>I would like to select from the purchasing history to form a list of product code available with the same product and same supplier, i.e.
>
>
>SELECT DISTINCT sCode,sDetail,sID,oMID FROM stockPurchase WHERE sID= ?lsID AND oMID = ?loMID ORDER BY sCODE
>
>
>However, it is more informative if I can add pCost & pDiscount for reference...
>
>
>SELECT DISTINCT sCode,sDetail,pCost,pDiscount,sID,oMID FROM stockPurchase WHERE sID= ?lsID AND oMID = ?loMID ORDER BY sCODE
>
>
>However, in that, even there is price difference, there will be additional record for that... so I don't want it...
Use GROUP BY instead of DISTINCT. For example, if you want a distinct record for every combination of sID and sCode:
SELECT sCode,sDetail,pCost,pDiscount,sID,oMID;
FROM stockPurchase;
WHERE sID= ?lsID AND oMID = ?loMID;
GROUP BY sID, sCode;
ORDER BY sCODE
Since this will give you an error message in newer versions of VFP, you either have to change SET ENGINEBEHAVIOR, or use summary functions for every field not included in the GROUP BY clause, for example:
select sCode, max(sDetail), min(pCost), max(pDiscount), sID...;
...;
group by sID, sCode
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)