Hi all FM Genius,
I have a table where I have as columns: Purchase_Month, p_SupplierName and c_Total from table::Purchases. I wrote an Executesql formula as follows:
ExecuteSQL (
"SELECT p_SupplierName , Purchase_Month, SUM(c_Total)
FROM Purchases
WHERE Purchase_Year = ?
GROUP BY p_SupplierName, Purchase_Month" ;
":" ;
"" ; Purchases::Purchase_Year; Purchases::Purchase_Month )
and am getting the result as
Results:
Acrux Ltd:November:39371.0976694915254236
All Goods Ltd:October:7328.3071186440677962
Anichem Pharmacy:February:724.08
Anichem Pharmacy:January:16678.2529661016949151
IBL HEALTHCARE:January:822.7627118644067796
Inicia Ltee:January:6424.576271186440678
Pharmacy A.E.Patel & Co.:February:2747
Unicorn (MSJ LTD):February:8547
What I want is that the SUM to appear in the same row for the p_SupplierName as follows: Anichem Pharmacy: January:16678.2529661016949151:February:724.08
and the same should apply to all the p_SupplierName if they are having records for more than one month.
Any help would be highly appreciated.