>Hi everybody,
>
>I have the following code (and similar for other statistics):
>

>************************************************************
>*  FUNCTION GetStatusDistribution()
>************************************************************
>*  Author............: Nadya Nosonovsky
>*  Project...........: Visual Collections
>*  Created...........: 12/16/2005  09:39:24
>*  Copyright.........: (c) Jzanus, 2005
>*) Description.......: Returns counts for each status code for each department
>*  Calling Samples...:
>*  Parameter List....:
>*  Major change list.:
>function GetStatusDistribution
>lparameters tcQueue_Name, tcClient_Name, tcProduct_Line, tcAlias
>if empty(m.tcAlias)
>	tcAlias = "c_AccountsByStatus"
>endif
>
>local lcSQL
>IF (EMPTY(m.tcQueue_Name) AND EMPTY(m.tcClient_Name) AND EMPTY(m.tcProduct_Line)) ;
>	OR m.tcQueue_Name + m.tcClient_Name + m.tcProduct_Line = "%%%"
>text TO lcSQL NOSHOW PRETEXT 7
>	SELECT VisCodes.cCode_Description as cFullName,
>	Trans.cDepartment_Code,
>	CAST(NVL(COUNT(Trans.cTrans_pk), 0) as Numeric(5,0)) as nCount
>	FROM Viscodes
>	INNER JOIN Trans on VisCodes.cVisCodes_pk = Trans.cStatus_Codes_fk
>	where Trans.cResolution_Codes_fk IS NULL
>	group by 1, 2
>ENDTEXT
>ELSE
>text TO lcSQL TEXTMERGE NOSHOW PRETEXT 7
>	SELECT Vis1.cCode_Description as cFullName,
>	Trans.cDepartment_Code,
>	CAST(NVL(COUNT(Trans.cTrans_pk), 0) as Numeric(5,0)) as nCount
>	FROM Viscodes Vis1
>	INNER JOIN Trans on Vis1.cVisCodes_pk = Trans.cStatus_Codes_fk	
>	INNER JOIN Trans_Employees_Queues on Trans.cTrans_pk = Trans_Employees_Queues.cTrans_fk
>	INNER JOIN Queue_Names on Trans_Employees_Queues.cQueue_Names_fk = Queue_Names.cQueue_Names_pk
>	INNER JOIN Client_Hospitals on Trans.cClient_Code = Client_Hospitals.cClient_Hospital_ID
>	INNER JOIN VisCodes Vis2 on Trans.cProduct_Line_Code = Vis2.cCode_Value
>	AND Vis2.cCategory_Description like "PRODUCT_LINE%"
>	where Trans.cResolution_Codes_fk IS NULL
>	and Trans_Employees_Queues.iActive_Flag = 1
>	and Queue_Names.cQueue_Name LIKE [<<m.tcQueue_Name>>]
>	and Client_Hospitals.cClient_Hospital_name LIKE [<<m.tcClient_Name>>]
>	and Vis2.cCode_Description LIKE [<<m.tcProduct_Line>>]
>	group by 1, 2
>ENDTEXT
>
>ENDIF
>
>return RunSQL(m.lcSQL, m.tcAlias)
>endfunc

>
>The Trans file has 724000 records. Do you know is it possible to speed up the counting?

VFP doesn't optimize group by,distinct,order by.

count a primary key
NVL(COUNT(Trans.cTrans_pk), 0)
in a INNER JOIN is equal to
COUNT(*).

You can try a partial count:

* FIRST
	SELECT VisCodes.cCode_Description as cFullName
	,      Trans.cDepartment_Code
	,      CAST(SUM(Trans.nCount) as Numeric(5,0)) as nCount
	FROM Viscodes
	INNER JOIN 
	(SELECT trans.cStatus_Codes_fk
	,	trans.cDepartment_Code
	,	COUNT(*) AS nCount
	    FROM Trans
		where cResolution_Codes_fk IS NULL
		GROUP BY 1,2) Trans
	on VisCodes.cVisCodes_pk = Trans.cStatus_Codes_fk
	group by 1, 2

* SECOND
SELECT Vis1.cCode_Description as cFullName
,	Trans.cDepartment_Code
,	CAST(SUM(Trans.nCount) as Numeric(5,0)) as nCount
    FROM Viscodes Vis1
    INNER JOIN 
    (SELECT	trans.cStatus_Codes_fk
       ,	trans.cDepartment_Code
       ,	COUNT(*) AS nCount
    FROM trans
    INNER JOIN Client_Hospitals on Trans.cClient_Code = Client_Hospitals.cClient_Hospital_ID
    INNER JOIN VisCodes Vis2	on Trans.cProduct_Line_Code = Vis2.cCode_Value 
    INNER JOIN Trans_Employees_Queues	on Trans.cTrans_pk = Trans_Employees_Queues.cTrans_fk
    INNER JOIN Queue_Names on Trans_Employees_Queues.cQueue_Names_fk = Queue_Names.cQueue_Names_pk
     where Trans.cResolution_Codes_fk IS NULL 
	and Client_Hospitals.cClient_Hospital_name LIKE [<<m.tcClient_Name>>]
	AND Vis2.cCategory_Description like "PRODUCT_LINE%"
	and Vis2.cCode_Description LIKE [<<m.tcProduct_Line>>]
	and Trans_Employees_Queues.iActive_Flag = 1
	and Queue_Names.cQueue_Name LIKE [<<m.tcQueue_Name>>]
     GROUP BY 1,2) Trans
     on Vis1.cVisCodes_pk = Trans.cStatus_Codes_fk
     group by 1, 2