Beth,
You are missing something here, the requirement for GROUP BY is to have all fields which are not involved in AGGREGATE functions, not in any functions. If you didn't care about some field (or you are very sure that field has the same value inb all records, put it in MAX() function


>Hi All,
>
> I am working on select statements done by other programmer in vfp6
> and trying to get them up to vfp8 standards. But I am having a
> problem with this one. If I put all the non-function fields in the
> group by section, I get too many records back. I tried surrounded
> all the char fields with PADR() to their size, and still get error -
> group by function missing or invalid.
>
> Any ideas on how I can fix this?
>
> origanial:
>

>  SELECT DISTINCT '*' AS LASTNAME, ;
>    Service_id, DESCRIPTIO, school, ;
>    C1, C2, C4, u_billdesc, u_billdist, firstname, studnt_id, ;
>    '*' AS LAST_NAME, ;
>    MAX(distsch) AS distsch, ;
>    totaldist, lname, school_nam , ;
>    '' AS REPFName, ;
>    1000000 AS wrkNbrDate, ;
>    100000 AS wrkServiceDate, ;
>    100000 AS wrkCSDate, ;
>    {//} AS wrkMaxDate, ;
>    {//} AS wrkMinDate, ;
>    REPLI(" ", 254) AS wrkOther, ;
>    0000.0000 AS WrkRCPD, ;
>    0000.0000 AS WrkACPD, ;
>    9999.99 AS  WrkBCPD, ;
>    0000.0000 AS wrkG, ;
>    0000.0000 AS wrkRealG, ;
>    0000.0000 AS wrkA, ;
>    0000.0000 AS wrkN, ;
>    000.00000 AS wrkPercent, ;
>    SUM(NVL(cost,0)) AS wrkRealCost, ;
>    SUM(NVL(basecost,0)) AS basecost, ;
>    0000.000000 AS wrkTG, ;
>    0000.000000 AS wrkTBase, ;
>    0000.000000 AS wrkTAdjust, ;
>    0000.000000 AS wrkAdjust, ;
>    0000.000000 AS wrkNoAdjust, ;
>    0000.000000 AS wrkTA, ;
>    0000.000000 AS wrkTN, ;
>    000.0000000 AS wrkBillPercent, ;
>    wrkStatus, ;
>    STATUS, period_tag, ;
>    0000.000000 AS wrkAddBaseCost, ;
>    000000 AS wrkCostDate, ;
>    0000.000000 AS wrkAdjustC, ;
>    0000.000000 AS wrkNoAdjustC, ;
>    0000.000000 AS wrkBasecostC, ;
>    0000.000000 AS wrkAdminC, ;
>    report_version AS wrkVersion, ;
>    00000 AS wrkTime ;
>    FROM (sAlias) ;
>    INTO TABLE bl731 DATABASE DatabaseBK;
>    WHERE (STATUS<>'Active' AND period_tag=sPeriod AND ;
>    (ALLT(Service_id) + ALLT(DTOC(DATE)) + ALLT(DTOC(date_appr)) ;
>    NOT IN (SELECT ALLT(Service_id) + ALLT(DTOC(DATE)) + ;
>    ALLT(DTOC(date_appr)) FROM loChangeCost))) ;
>    OR (DATE >=CTOD(sMinDate)) OR (wrkStatus=2);
>    GROUP BY u_billdesc , Service_id , studnt_id , wrkStatus ;
>    ORDER BY u_billdesc ASC, Service_id ASC, studnt_id ASC, ;
>    wrkStatus ASC, DATE ASC
>

>
>TIA