>>Ok, what is better then:
>>
>>select cTrans_pk, cUserID, cUsGrLink_pk, yBalance from ... where ... into cursor curTemp
>>
>>select count(cTrans_pk) as iAccounts, sum(Balance) as Balance, cUserID from curTremp group by cUserID
>>
>>or
>>
>>select count(cTrans_pk) as iAccounts, sum(Balance) as Balance, cUserID from ... where ... group by cUserID
>>
>>E.g. do it in two steps or in 1 step?
>
>if you uses curTemp only once the second it is better

Hi Fabio,

I have several management statistics forms. The first form is for Executive Managers who see all the departments statistics. The second level is for particular department, e.g. the statistics is limited by department. The third level is by User's team and the fourth level is for particular user. Each form also has an ability to switch view, e.g. you can see statistics by department, by user, by team in the first screen, by user or by team in the second screen (the rest of the screens don't change their view).

Originally I was pulling all data for all users in every form regardless of the fact, I was looking for all users or for subset of users. Recently I had a chance to review the logic, this was the first thing I changed, e.g. I've added a where condition (after some deliberation what is better - join or in (select ...)) to limit number of records. But since I need counts only in my forms, I decided to go one additional step further and do group by immediatelly instead of pulling all records and then counting them. I expected to see some speed gain, but I think I don't see it. Do you think I'm still right in doing this in one step rather than two? Does VFP creates this big result set in memory before group by? Is there any command to optimize this?

Thanks again for your suggestions.