>I think we have a winner! Now explain to me why those fields are NOT considered part of an aggregate function, if you would!
>

Hi Jim,

 ROUND(SUM(...)*facilitydata.facrebate*0.01,2) AS facility_rebate,  ;
 ROUND(SUM(...)*facilitydata.discountrate*0.01,2) AS employee_disc,  ;
 IIF(facilitydata.payroll,"Yes","No ") AS payroll 

ROUND and IIF are not aggregate functions, thus those three columns from facilitydata table are not aggregated.

Thanks,
Aleksey.




>>Hi Jim,
>>
>>I don't know how did you try, but it sure works for me.
>>
>>

>>CLOSE DATABASES all
>>
>>CREATE CURSOR facilitydata ;
>>(facilityid I,;
>> facilityname c(10),;
>> facrebate I,;
>> discountrate I,;
>> payroll L)
>>
>>CREATE CURSOR invoices ;
>>(custnumid I,;
>>invdate date,;
>>cashpaid Y,;
>>payroll Y,;
>>ccard Y,;
>>pcheck Y,;
>>salestax Y,;
>>shipcost Y)
>>
>>CREATE CURSOR customers ;
>>(custnumid I, ;
>>facilityid I)
>>
>>
>>SELECT facilitydata.facilityname AS facility_name, invoices.invdate AS show_date,  ;
>>  SUM(invoices.cashpaid+invoices.payroll+invoices.ccard+invoices.pcheck-invoices.salestax-invoices.shipcost) AS TOTAL,  ;
>>  ROUND(SUM(invoices.cashpaid+invoices.payroll+invoices.ccard+invoices.pcheck-invoices.salestax-invoices.shipcost)*facilitydata.facrebate*0.01,2) AS facility_rebate,  ;
>>  ROUND(SUM(invoices.cashpaid+invoices.payroll+invoices.ccard+invoices.pcheck-invoices.salestax-invoices.shipcost)*facilitydata.discountrate*0.01,2) AS employee_disc,  ;
>>  IIF(facilitydata.payroll,"Yes","No ") AS payroll ;
>>  FROM  facilitydata ;
>>  inner JOIN customers    ;
>>  inner JOIN invoices    ;
>>  ON  customers.custnumid = invoices.custnumid    ;
>>  ON  facilitydata.facilityid = customers.facilityid;
>>  GROUP BY 1,2,facilitydata.payroll,facilitydata.facrebate, facilitydata.discountrate;
>>  ORDER BY facilitydata.facilityname, invoices.invdate
>>

>>
>>Thanks,
>>Aleksey.